There are the following entities:

Employees

• employee_id (PRIMARY KEY)
• first_name
• last_name

Customers

• customer_id(PRIMARY KEY)
• first_name
• last_name
• login
• password

Products

• product_id(PRIMARY KEY)
• product_name
• type
• price
• units_in_stock

Orders

• order_id (PRIMARY KEY)
• employee_id
• completion_date

Cart_items

• customer_id(PRIMARY KEY and FOREIGN KEY ref. to Customers)

• product_id(PRIMARY KEY and FOREIGN KEY ref. to Products)

• order_id (FOREIGN KEY ref. to Orders (CAN BE NULL!))

• quantity

  In my application I have the following logic for customer: Today customer adds some products to his cart(using his UI), then if he decides so, he checkouts an order with the employee. And also if he decides so, he do not checkouts the order today, but tomorrow he want just to see his cart and decide something else etc.

  Basic implementation: To see shopping cart of some customer we just execute the following query: SELECT * FROM Cart_items WHERE customer_id = some AND order_id IS NULL; To see an orders history of some customer we just execute the second one: SELECT * FROM Cart_items WHERE customer_id = some;

Main question is:

"Is it ok to contain NULL values in order_id of Cart_items, when order is not created?" I mean:

1. is it optimal, or is there a better way to organize the table structure in a database?;
2. does this structure satisfy the three normal forms?.

I saw this topic: Can a foreign key be NULL and/or duplicate?. But there is no mention of forming a shopping cart structure.

The design is more or less okay. However right now, it looks like cart_items is dependent on orders which is not exactly true. If the use case needs to be extended to include shipping information, it would look weird to have shipping_id in cart_items.

Usually a cart is a temporary place for the user to place the items he intends to buy. Once he decides to move forward with the purchase, an order is created with the items in the cart and the cart is emptied.

Mixing ordered items and cart items in the same table is not a good approach. Cart items needs to be accessed fast, hence the table should not be too big in size. Storing ordered items and cart items, will definitely have detrimental effects on performance on the long run. Cart items are also subjected to deletion operations while ordered items are never deleted, a simple bug in the app can also delete ordered items.

A better approach would be to have a separate table order_items which keeps track of the items of an order.

Order_items

• order_item_id (PRIMARY_KEY)
• product_id (FOREIGN KEY ref. to Products)
• order_id (FOREIGN KEY ref. to Orders)
• quantity

An order cannot exist on its own, having customer_id in orders would make sense.

Orders

• order_id (PRIMARY KEY)
• customer_id (FOREIGN KEY ref. to Customers)
• employee_id
• completion_date

While the table cart_items are just the items the user has in his cart.

Cart_items

• customer_id (PRIMARY KEY and FOREIGN KEY ref. to Customers)
• product_id (PRIMARY KEY and FOREIGN KEY ref. to Products)
• quantity