CODEWITHSUNDEEP
sqldatabase-designforeign-keys
developer
developer

Reputation: 1673

SQL table design with foreign key

I have two tables:

Product

Columns: Product ID (PK), Product Name

Order

Columns: Order ID (PK), Order Name

I would like to store which Product Id for each order.

Is it better to create another table:

ProductOrder table:

Order ID (FK), Product ID(FK)

or add an additional column in the Order table:

Order ID (PK), Order Name, Product ID (FK)

Edit: Order can only contain one product.

Upvotes: -2

Views: 54

Answers (2)

marcothesane
marcothesane

Reputation: 6721

My suggestion is a mix of @ValNik 's comment and @Daniele 's answer:

CREATE TABLE orders (
-- these columns, plus any needed at order level
  order_id INT NOT NULL PRIMARY KEY
, cust_id  INT NOT NULL FOREIGN KEY REFERENCES(customer)
, order_ts       TIMESTAMP NOT NULL
, order_discount DECIMAL(10,2)
, order_pctoff   DECIMAL(3)
);

CREATE TABLE orderitem (
-- these columns come to mind, there might be others - at item level
  order_id INT NOT NULL FOREIGN KEY REFERENCES(orders)
, prod_id  INT NOT NULL FOREIGN KEY REFERENCES(products)
, quantity      INT NOT NULL
, item_discount DECIMAL(10,2)
, item_pctoff   DECIMAL(3)
, PRIMARY KEY (order_id,prod_id)
);

Upvotes: 0

Daniele
Daniele

Reputation: 236

The best approach depends on the nature of the relationship between Product and Order in your application. If there is any possibility of an order containing multiple products now or in the future, option 1 (ProductOrder table) is the better choice. It keeps your database design normalized and avoids potential issues if your requirements change in futuer. Option 2 (Order Table)can be a simpler and efficient solution at this moment but normalization often wins in most cases where scalability and flexibility are priorities!

Upvotes: 2

Related Questions