Let's add a shopping cart system for a Clown Store we're starting. This system will have a customer table, a customer orders table, an order line items table, and a product table.

This section is mostly to get our tables and data set up so that we can start creating some queries to experiment with.

Let's get a glimpse of how this all lays out. I'm using Diagrams.net to create the following visualization of the database. We're also going to add Foreign Keys into the mix so that you can get familiar with how that works.

We're going to create a new database outside of our employees database. We'll link tables between the two different databases. We only need to do that when an employees processes an order, meaning the employee has processed and shipped the order.

The lines connect where the tables will join. We'll also get our three other employee tables into the mix here, showing how the employees will connect with this system.

Here's the actual schema definition.

-- create the database
create database shoppingCart;

-- use the database
use shoppingCart;

Then we'll create the tables within our new database.

-- each customer
create table customers (
	id int unsigned not null,
	name varchar(128) not null,
	address varchar(128) not null,
	city varchar(128) not null,
	state char(2) not null,
	zip varchar(12) not null,
	isDeleted boolean not null default false
) engine=innodb default charset=latin1;

-- add a primary key
alter table customers
  add primary key (id);

-- make this field auto increment
alter table customers
  modify id int unsigned not null auto_increment;

-- each overall order for a customer
create table customerOrders (
	id int unsigned not null,
	orderDate date not null,
	customerId int unsigned not null,
	processedByEmployeeId int unsigned not null default 0,
	isDeleted boolean not null default false
) engine=innodb default charset=latin1;

-- add a primary key
alter table customerOrders
  add primary key (id);

-- make this field auto increment
alter table customerOrders
  modify id int unsigned not null auto_increment;

-- create a foreign key to enforce a link between customer.id and customerOrders.customerId
alter table customerOrders
	add constraint customerFK
	foreign key (customerId)
	references customers(id)
	on update cascade
	on delete cascade;

-- each product and it's price
create table products (
	id int unsigned not null,
	description varchar(252) not null,
	finish varchar(20) not null,
	price decimal(10,2),
	isDeleted boolean not null default false
) engine=innodb default charset=latin1;

-- add a primary key
alter table products
  add primary key (id);

-- make this field auto increment
alter table products
  modify id int unsigned not null auto_increment;

-- each line item of an order
create table orderLineItems (
	orderId int unsigned not null,
	productId int unsigned not null
) engine=innodb default charset=latin1;

-- add foreign key back to customerOrders
alter table orderLineItems
	add constraint customerOrderFK
	foreign key (orderId)
	references customerOrders(id)
	on update cascade
	on delete cascade;

-- add foreign key forward to products
alter table orderLineItems
	add constraint productFK
	foreign key (productId)
	references products(id)
	on update cascade
	on delete cascade;

Now, let's add some test data to each table.

insert into customers (name, address, city, state, zip) values 
	("Crab Rock", "1234 Main St", "Los Angeles", "CA", "90000"),
	("Commander Riker", "4321 Good St", "Los Angeles", "CA", "90001"),
	("InfiniteBliss6969", "4576 Better St", "Los Angeles", "CA", "90002"),
	("Agent Smith", "1234 Trying Dr", "Los Angeles", "CA", "90001"),
	("Jimboy Dingus", "5678 Crazy Town St", "Los Angeles", "CA", "90010"),
	("Smella Kella", "5678 North Dr", "Polka", "CA", "90000"),
	("Donkey Woman", "543 Asdf Wy", "Visia", "OR", "50000"),
	("Sara Smalls", "56743 Iso Dr", "Camden", "WA", "60000"),
	("Gib Bigger", "433 Nine Dr", "Camden", "WA", "60000"),
	("Modern Homes", "59032 Main St", "North Bend", "IN", "80000");

insert into customerOrders (orderDate, customerId) values
	("2020-01-01 ", 1),
	("2020-01-02", 1),
	("2020-01-10", 9),
	("2020-02-07", 7),
	("2020-03-01", 3);

insert into products (description, finish, price) values 
	("Large Floppy Toothbrush With Suds", "Plastic", 19.95),
	("Large Floppy Clown Shoes", "Rubber", 19.95),
	("Vibra-Glove Trick Buzzin Glove", "Rubber", 29.95),
	("Sense-attentive Electrocute Your Friends!", "Rubber", 49.95),
	("Blow Up Balloon Animal Balloons", "Rubber", 8.95),
	("Hand Cuffs", "Metal", 10.88),
	("Hot Slippery Lube", "Liquid", 2.56),
	("Rainbow Clown Suit", "Fabric", 5.95),
	("Clown Wig", "Hairy", 7.95);

insert into orderLineItems(orderId, productId) values 
	(1, 1),
	(1, 6),
	(2, 3),
	(3, 3),
	(3, 4),
	(3, 6),
	(4, 2),
	(5, 5);

Most of these tables should look pretty familiar. The customers table should look similar to your employee table. You can also see that the products table is pretty standard. In this project we're going to introduce the concept of an intermediary table. This new table, orderLineItems, lives between customerOrders and products.

This creates a many to many connection between the two tables. This means that many customerOrders can point to many products. This allows you to have one customerOrder that links to many possible products. This allows us to then do calculations on our data. For example, we'll be able to calculate a total for a customer order. You can create as many customerOrders that point to as many products as you need utilizing this type of intermediary connector table.