0

I was just after some input on database design. I have two tables, Orders and Items.

The items table is going to be a list of items that can be used on multiple orders, each item has an id

The way i thought to do it at the moment, was in the order to put an array of comma seperated ids for each item in the order.

does that sound like the best way? also im using linq to entity framework and i dont think id be able to create a relationship between the tables, but i dont think one is needed anyway is there, since the items are not unique to an order

Thanks for any advice

AlexW
  • 2,843
  • 12
  • 74
  • 156

3 Answers3

3

The way I thought to do it at the moment, was in the order to put an array of comma separated ids for each item in the order. Does that sound like the best way?

Absolutely not - It will be MUCH more difficult in SQL to determine which orders contain a particular item, enumerate the items (to get a total, for example), and to add/remove items from an order.

A much better way would be to create an OrderItem table, which has a foreign key back to Order and Item and any other attributes relating to the item in that order - quantity, discount, comments, etc.

As far as EF goes, it will probably create a third entity (OrderItem) that will "link" the two tables. If you don't add any extra properties (which you probably should) then EF will probably create it as a many-to-many relationship between the Order and Item entities.

D Stanley
  • 149,601
  • 11
  • 178
  • 240
2

As far as I have understood from your question (it is not very clear), every Order can have multiple Items and every Item can be used in multiple orders. If this is what you want, you have a many to many relationship, that must be resolved using an intersection entity. This intersection entity has 2 foreign keys, one for item and one for order. Using it, you can identify what items are in a certain order and what orders need a certain item.

As my explanation is very short and very sloppy, I will recommend you the following references:

http://sd271.k12.id.us/lchs/faculty/bkeylon/Oracle/database_design/section5/dd_s05_l03.pdf

Resolve many to many relationship

Also, you proposed design is very bad, as it breaks the first normal form: no attribute can have multiple values. You shoud try to build databases at least in third normal form.

Community
  • 1
  • 1
Paul92
  • 8,827
  • 1
  • 23
  • 37
1

Regarding the database design, you would usually create a third table - ORDER_ITEMS - linking the two tables, containing columns (foreign keys) for order id and item id. You might also want to include a column for quantity.

Andrew Cruickshank
  • 91
  • 3