Search⌘ K
Home
Courses
Getting Started with SQL and Relational Databases

Create the orders Table

Understand how to create the orders table in a MySQL database, define foreign keys to maintain relationships with customers and products, and enforce data constraints for a CRM system.

We'll cover the following...

Introduction

We’ll continue the creation of our simple customer relationship management system. We already created the customers and products tables, and we’re now ready to create two more tables, orders and order_items.

Model details

The orders table holds the header of the order:

  • It has a reference to the owning customer by the customer_id column. This is going to be a foreign key. Given one order, we can locate the single customer that the order belongs to. That is why the order-to-customer relationship is a one-to-one relationship. On the other hand, given one specific customer, we can locate more than one order that belong to the particular customer. Hence, the customer-to-orders relationship is a one-to-many relationship.

  • Besides that, orders also have the order_number. This will be mandatory and will have a string representation like ORD123. It will also be ...