Composite Keys: Good or Bad?
Composite Keys: Good or Bad?
(OP)
I currently use Access 2007 and designing a database that appears to be simple.
My question is general in nature though. I feel I have a good natural composite key but I realize that I could use an auto-number type primary key field.
What are the pros and cons here? How do I know from the start which way to go? I have done my research with the client and understand the 'rules' for the situation well enough to create a composite key of three fields.
Any help would be greatly appreciated.
My question is general in nature though. I feel I have a good natural composite key but I realize that I could use an auto-number type primary key field.
What are the pros and cons here? How do I know from the start which way to go? I have done my research with the client and understand the 'rules' for the situation well enough to create a composite key of three fields.
Any help would be greatly appreciated.
RE: Composite Keys: Good or Bad?
Duane
Hook'D on Access
MS Access MVP
RE: Composite Keys: Good or Bad?
"a composite key of three fields."
I hope you made sure those fields are 'set-in-stone' and never, ever change for any reason, right?
I would be still tempted to use an auto number anyway. I had too many cases where customer say: "Yes, it will never change" and come back and change the rules on me.
Have fun.
---- Andy
RE: Composite Keys: Good or Bad?
--Lilliabeth
RE: Composite Keys: Good or Bad?
I'd be inclined to add an autonumber field as a surrogate because it's usually low cost. If however you use the surrogate for joins, then you've got the complication of propagating it to tables that reference it.
RE: Composite Keys: Good or Bad?
You have two tables: Orders and OrderLineItems. The Orders table will have a PK of OrderNumber, which is an AutoNumber. The OrderLineItems has a composite key: OrderNumber + ItemID + ShipLocation (because you ship to multiple locations on one order). Your employer has been very successful, and there are over 10 million records in the OrderLineItem table. Joining or filtering on the composite key will be very efficient because the records are sorted by these fields. Lets say you have used an AutoNumber field as the primary key. Finding a record by the OrderNumber + ItemID + ShipLocation attributes could take a long time since may not be in order.
Composite keys are not inherently bad. Sure, they can make programming the UI a little more complicated. However, it can cause real headaches if you don't configure your tables correctly in the first place.
GTG