Aaron has a different perspective on the value of always having an ID column as primary or surrogate key on every table. He writes:
I do take issue with having only a composite key in place instead of an identity column. Why? Because writing queries against tables that have composite keys of 4 columns, is a total pain in the neck. It also requires much more testing to ensure that the joins are behaving as intended. If there is a single identity column and an inner join to another table that references this record, I only have to test two things (if there is a match in the join clause, expected result A, else result B). I would much rather have an identity column on the table, include the composite key information in the table (which should be there anyway) and enforce uniqueness via a constraint of my choosing. This way if i need to reference this record in another table, i’m not dragging around the same 4 composite key values to any/all other table i want to relate to this table. Now, if Ravi is saying he’d prefer the composite key as the primary key and have an alternate id/surrogate for referencing in other tables, then i may be swayed back to his side of things.
I have worked with databases designed without having a composite key, using an id column as a primary key instead. I remember describing to my boss how I needed to join one table to another. Having to traverse the data model through the ID columns made the join extremely painful. If composite keys had been used I could have simply made a three table join to get the data required. However, using ID Only foreign keys required I join nearly 20 tables to get my final result. It was like trying to cross the street, but having to go completely around the whole city before arriving due to a large number of one way streets.
That doesn’t mean that there is never a use for an Auto Number system assigned value. However, when a table may consist of composite a key as a natural key that is system assigned from foreign key relations of ancestors, then there is little to no value of having an additional single id column.
In my experience, Aaron makes a good point about having extra work to join on composite keys. With autocomplete today that is less of an issue. When it comes to working with a lot of id columns, I have found composite key relationships to be a lot easier when joining ancestor tables. And the additional flexibility of being able to join many other tables directly without having to traverse a large number of relationships far outweighs a little bit of typeing. The performance boost and reduction of necessary indexes is an immediate win.
Let’s say you have four tables. Table A has AId, table B as Bid. Table C is a many to many join of Table A and Table B having a composite key of Aid, Bid. Table D has Did as a unique value, and is related to Table C, so also has Aid and Bid. Because table D has Aid and Bid, it can be joined with no extra effort to Tables A, B, and C. It does not have to go through C to get to A and B. This example can go many levels deeper…but provides clarification to my point.
The exceptions I see in having a single unique sequential number is to assist in the performance capabilities of the database engine such as SQL Server, or perhaps for change tracking. Combining the table name with the unique row id you can easily track every column in every table for change using a single data structure.
Keep me honest here…does my example make sense to you? If so, which kind of join is more often the case? Is it easier to go around the city sometimes, or to be able to make direct associations with ancestor relationships? Share your experience by email to btaylor@sswug.org, or better yet, leave your comment here online.
Cheers,
Ben