CODEWITHSUNDEEP
databaseerd
f4r4
f4r4

Reputation: 573

Database design Composite Key dilemma

When designing a table for a many to many relations ship 1 sides primary key will come to the [M] side the middle table, my current middle table is 

Salesline : Itemid(PK), SOId(PK), userid(PK)

or 

Salesline : Itemid(PK), SOId(PK), 
userid_salesorder(PK) , // links to the salesorder.userid attribute 
userid_item(PK) // links to the item.userid attribute

Which is the correct way ?

ERD

Upvotes: 0

Views: 1655

Answers (1)

Olaf
Olaf

Reputation: 6289

Looks like the natural realtionships between your entities are:

  • A User can place 0, 1 or more SalesOrders
  • A SalesOrder may include 1 or more SalesLines
  • A SalesLine corresponds to exactly 1 Item
  • An Item can be ordered on many SalesOrders

I would recommend to break the realtionship between the User and Item tables and to move the quantity of the particular item ordered to the SalesLine table. User, SalesOrder and Item should have simple, possibly surrogate, primary key. The primary key of the SalesLine may be composite, consisting of the foreign key to the SalesOreder table and, say, a line number within the order.

Upvotes: 1

Related Questions