CODEWITHSUNDEEP
database
totalitarian
totalitarian

Reputation: 3666

How store order and products in a database?

I have an order with details such customer id, order date, order type etc...

The order then contains a list of products on that order

Do I create one table for the order and then another table for the products?

If so, should there then be foreign keys between the 2 tables?

Upvotes: 1

Views: 3127

Answers (3)

Rahul
Rahul

Reputation: 77886

Your table structure should look like below. In your product table create a reference to order_id in order table by means of foriegn key. Thatway, each order will be associated with one or more products and you can find out the same using a JOIN in your SELECT query.

Table: Order(order_id Primary key,customer id, order date, order type)

Table: Products(product_id primary key, order_id foriegn key,col1, col2);

Explanation:

A product may not be associated with a order at all time but a order must be associated with a product (Obhious reason, we can't have a blank order).

Upvotes: 1

Muhammad Ali
Muhammad Ali

Reputation: 2014

You current condition may works if the product is not belong to your store/source , you are getting products from different source and client order the product so you can maintain the order with the require details. if the product came from your source/store then you must need the product and order

Upvotes: 1

duffymo
duffymo

Reputation: 308848

Here's how I'd model it:

  1. Customer can have many Orders; an Order belongs to one Customer.
  2. An Item refers to one Product
  3. An Order can have many Items; an Item belongs to one Order.

One-to-many foreign keys always belong in the "many" table and refer back to their "one" table.

If you have many to many relationships you need a JOIN table, which has two one-to-many foreign keys.

Upvotes: 5

Related Questions