Composite Keys: Good or Bad?

[JOEYB99]

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.

 

[dhookom]

I always use an Autonumber primary key. I may set unique indexes on other fields to build in some integrity.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

"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

 

[Lilliabeth]

Composite primary keys complicate things later, when building the front end. Even a simple combo box is made more complicated. We have a rule against composite PKs where I work. Unique index the fields, but use an autonumber as the join field.


--Lilliabeth

 

[BNPMike]

If you have a composite key, you have a composite key. There's nothing you can do to change that. Whether you tell Access, or ask it to do anything about it, is a separate issue. You will still be able to use it as a key.

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.

 

[GregTheGeek]

Let's look for a moment at what, exactly, a primary key is. "The primary key of a relational table uniquely identifies each record in the table." (

http://databases.about.com/cs/administration/g/primarykey.htm)

If, by the nature of your data, multiple fields are required to uniquely identify a record, then you have a composite key. If you have a composite key, your table should be configured accordingly. Adding an Identity (AutoNumber/AutoIncrement) field only masks the need for a composite key. In fact, using an Identity field, in place of a composite key, could hurt performance. In a table with a primary key defined, records are stored so that the PK fields are always sorted. Let's look at an example we can all, hopefully, relate to.

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