I would like to know the best practice for designing the database for an Order Management System. I have an order table and orderitems table. My current design is as follows.
Order table orderid int primary key totalprice int orderstatus int Order Items table orderitemid int primary key orderid int foreign key productid int quantity int
The orderid is the foreign key here. An order a number of order items in the order items table. Its a one to many relationship.
Is this the best method for designing the tables or should is use the below design
Order table id int primary key orderid int orderitemid int foreign key totalprice int orderstatus int Order Items table orderitemid int primary key productid int quantity int
Please describe the advantages and disadvantages in using both designs. Also let me know if some other better designs are possible.
Option 1 is the only reasonable option
As 1 Order has many Order Items, the orderId is held once in Orders and many times in Order Items.
With Option 2, this relationship is possible but you would be duplicating data unnecessarily, as you wouldn't be able to have many OrderItemId values against one record in Order.
This is basic normalisation.