Relationship between Customer and Order in a Shopping Cart_问答_开发者

I'm making an ERD, so that I can build my shopping cart.

I am confused about the relationship between Order and Customer.

If im not mistaken, a customer can order many products,

an order can be placed by 1 customer

Create Table OrderProduct(
orderProductId int PRIMARY KEY,
productID int,
Quantity int
)

Create T开发者_如何学编程able Orders( 
OrderId int PRIMARY KEY,
orderProductId int, //foregin key
CustomerId int,
date
)

Am I correct, or is mu table structure wrong?

The Order table has a column orderProductId - which makes no sense; if you think about it that means an Order will have one OrderProduct.

The OrderProduct table should have a column, OrderId - a foreign key relating to the primary key of the Order table. That way you could have 10 OrderProduct records (commonly called order lines) for the same order.

With these issues I find it helps to mock up some test data - you'd see pretty quick if it would work out.

Here is simple and slightly heretic suggestion for a shopping cart.

table cart(
   customer_id not null
  ,create_date not null
  ,primary key(customer_id)
  ,foreign key references customers(customer_id) on delete cascade
);

table cart_item(
   customer_id   not null
  ,product_id    not null
  ,quantity      not null
  ,modified_date not null
  ,primary key(customer_id, product_id)
  ,foreign key(customer_id) references cart(customer_id) on delete cascade
  ,foreign key(product_id)  references products(product_id) on delete cascade
);

Here is the rationale behind the design:
If what you are building looks anything like most shopping sites, there will only be 0-1 active carts at a time per customer. Therefore, the cart can inherit the customer key.
Usually, one does not buy a product 2 times. Instead one buys 2 pieces of the same product, so I made the product key part of the primary key in the Cart Item table.

Either the customer decides NOT to proceed with the stuff in his cart. In this case, the cart and the items will just stay in the table until he either comes back and decides to go ahead and pay up, or until you decide (for example based on cart.create_date) that it is time to remove the data.

When the customer proceeds to checkout and finalizes his order, you can create the real order and orderlines (products), including the order date which is not necessarily the same as the date when the customer began to stick items in the cart. (My book purchases on amazon typically takes a week to finalize because I keep finding interesting books and I have to read some reviews first :)

As for what keys your order/orderline table should have, it actually depends on lots of things such as:

Is it possible to delete products that have orders?
Do you need to accurately represent the product information (price, name) as of the time of purchase?
Do I use the orderline table to represent other stuff such as discounts/campaign codes etc
Do I need to accurately report the Customer shipping address as of time of purchase? (customer moved to another country, but you sent the items to his old place)

The above design is using natural keys, which happens to provide excellent performance in this case.

That seems fine to me, but you need a OrderId in the OrderProduct table in order to link the order details to the order - drop the OrderProductId from Orders.

Some of the other columns like ProductId and CustomerId should also be foreign keys, of course.

Is an "Order" a finalized order or is there a later invoicing step? Because you typically may want to lock in the unit price at the order (from the product file at the time of the order, or perhaps from a signed/approved quote).

An order can have multiple products.You can remove the orderProductId from OrderProuct table and make the productId as the primary key.Also have the OrderId as the foriegn key in the OrderProduct table so that you can have a mapping between the Order and the products in the respective order.