Composite Keys versus a single arbitrary PK

[johnbdh]

I am in the process of learning SQL using SQL Server. The relational database I am coming from did not support composite keys. In creating my first test DB in SQL I found that I was using composite keys like a kid with a new toy.

My question to the forum is, what are the pros and cons to using composite keys as opposed to using a single arbitrary primary key. When are composite keys appropriate and when not.

Given a simple DB with 4 tables - Buildings, Floors, Rooms, RoomDetails. I created the folowing schema:

buildings
building_ID (PK)

floors
level_number
building_ID (FK)
PK(building_id,level_number)

rooms
room_id
level_number (FK1 refers to floors level_number)
building_ID (FK1 refers to floors building_ID)
PK(building_id,level_number,room_id)

room_details
partitions
room_id (FK1 reers to rooms room_id)
level_number (FK1 refers to rooms level_number)
building_id (FK1 refers to rooms building_id)
room_code (PK char(12) "bldgID-levelNum-roomID-partition&quot


In my previous DB environment I would have done:

buildings
bldg_sequence (PK IDENTITY(1,1)
Building_ID

floors
floor_sequence (PK IDENTITY(1,1)
level_number
bldg_sequence (FK)

rooms
room_sequence (PK IDENTITY(1,1)
room_id
floor_sequence (FK)

room_details
partitions
room_sequence (FK)
room_code (PK char(12) "bldgID-levelNum-roomID-partition&quot

It seems to me that the example NOT using composite keys would work just as well and would be simpler to maintain.

Will one or the other adversely affect the creation of a front end or a user's ad hoc queries?

Thanks for any insight any one may have on this topic.

John

 

[Terpsfan]

I think the rule of thumb is: keep it simple. Only use a composite key if you have to. Composite keys are useful if you can only get a unique record by the combination of two primary keys. And since each primary key field requires an index, you are bloating the size of the database when using indexes on two primary keys in a table.