Database design tip?

[tagarciap]

Hello,

Im designing a database and find that I seem to have a few small tables with say, 3 or 4 fields, where all of those fields are required to identify a record. Is it better to make a composite primary key consisting of all the fields, or introduce a non-composite primary key, like an auto_increment integer?

Thanks for your help!
Keith

[guelphdad]

if you have a natural key it is better to use it than introduce an unnecessary key. however you also need to take into consideration how you would use that data between other tables. Basically I'd use the multi-column primary key but ...

if I knew I was going to use the primary key to enter data into another table then I'd consider the surrogate key.

say you have albums and artists in your first table, you could designate the two columns as a primary key because most artists don't have multiple albums with the same name. But if you were then going to introduce a tracks table it might make more sense to use an autoincrement column and then use the last_insert_id for that column to enter values into your tracks table.