I would start saying that a composite key is not the opposite of a surrogate key, but I guess you already know that. A composite key is a key that is composed of more than one attribute, so a composite surrogate key could exist (although it would not make any sense). On the other hand, there are natural keys (the opposite of surrogate keys) that are not ‘composite’. So I think we should discuss the pros and cons of surrogate keys vs. natural keys (whether they are composite keys or not). That said, <b>in my opinion</b>, the advantages of surrogate keys include: - They are not ‘coupled’ to the business, so a change in the business does not necessary means changes to your primary key values. - If the surrogate key will substitute a composite natural key, and you are going to have foreign keys on other tables referencing this key, you just need to ‘replicate’ one column to the referencing tables. This could save some space, and could make join queries simpler. The disadvantages include: - You need to create new columns to work as surrogate keys - Surrogate keys don’t have any business meaning. Looking at their values doesn’t give you a clue of what they relate to. - You might need to add additional indexes to improve performance when using the ‘natural’ columns to perform searchs. As for the natural keys, the advantages of them are the disadvantages of the surrogate ones, and vice versa. What’s better ? It depends. Depending on the information you are going to store in the table, you should decide which approach works best for you. Personally, I tend to use surrogate keys in most cases, but not always. The most important factor I consider is the possibility of change of the natural candidate keys. ----------------------------------------------------

I would start saying that a composite key is not the opposite of a surrogate key, but I guess you already know that. A composite key is a key that is composed of more than one attribute, so a composite surrogate key could exist (although it would not make any sense). On the other hand, there are natural keys (the opposite of surrogate keys) that are not ‘composite’. So I think we should discuss the pros and cons of surrogate keys vs. natural keys (whether they are composite keys or not). That said, <b>in my opinion</b>, the advantages of surrogate keys include: - They are not ‘coupled’ to the business, so a change in the business does not necessary means changes to your primary key values. - If the surrogate key will substitute a composite natural key, and you are going to have foreign keys on other tables referencing this key, you just need to ‘replicate’ one column to the referencing tables. This could save some space, and could make join queries simpler. The disadvantages include: - You need to create new columns to work as surrogate keys - Surrogate keys don’t have any business meaning. Looking at their values doesn’t give you a clue of what they relate to. - You might need to add additional indexes to improve performance when using the ‘natural’ columns to perform searchs. As for the natural keys, the advantages of them are the disadvantages of the surrogate ones, and vice versa. What’s better ? It depends. Depending on the information you are going to store in the table, you should decide which approach works best for you. Personally, I tend to use surrogate keys in most cases, but not always. The most important factor I consider is the possibility of change of the natural candidate keys. ----------------------------------------------------