confusion on usage of composite key in a table

Question

confusion on usage of composite key in a table

allan chuah

Grasshopper

Points: 12
More actions
November 16, 2008 at 6:09 am

#196080

"An shop receipt contain several items sold by a customer. Each items sold are listed with price"
I have an scenario as above. I got plenty of comments about the tables and its relationship:
1.
receipt (receiptID, receipt_sumtotal, payby, payon)
receiptitem (receiptID (FK), receiptitemID, itemprice, itemquantity, totalprice)
2.
receipt (receiptID, receipt_sumtotal, payby, payon)
receiptitem (receiptitemID, receiptID (FK), itemprice, itemquantity, totalprice)
I believe the first and the 2nd solution are acceptable, but it made me confuse when the composite key to be used appropriately. It would be glad if someone could explain me about it.
Thank.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Generally you'd use a composite key if there is no single column that uniquely identifies the row.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 734522 More actions · Answer 2

Moved to a better forum.

A PK is used to uniquely identify a row. If you need 2 (or more) fields to do this, you have a composite or compound key. These would move through to the FK in child tables.

allan chuah Grasshopper Points: 12 More actions · Answer 3

thank for the great replies from u guys. It makes senses now and clear up my frustrating mind on the question 🙂

Regards.

Josep SSCommitted Points: 1518 More actions · Answer 4

Nice question 🙂

Usually I do myself and I get Gail answer: better the composite key solution. A reason I've is that probably a large number of queries will be to get the receipts items for an asked receipt. So you'll use the clustered index rather than a non-clustered one so you'll have a better performance.

But where I'm working we work usually on the way, so there's no planning at all and can be changes in analysis and I've found that the 2on choice (just the receiptItemID as PK) is more versatile on changes. So, sometimes I use it.

But I also have a pair of questions:

When you've this relation in chain (I mean the ReceiptItem has another 1..N relation and this one another 1..N). Then the primary key can have many columns and I'm not sure the performance you could get and the queries (when you make JOINs) are larger to type and more difficult to read. What would you do in this cases?

In the 2on case (the composite key) the receiptitemID is an IDENTITY and you don't care about it or it's a sequencial number that starts by 1 for every receipt?