Under what condition do we need to use composite keys in the database

Question

I saw that we can have compound keys, where the primary key consists of the combined primary keys of two tables.

Like people and books

person_id and book_id will make the primary key. 

But I want to ask that we need hard code for programming langauge

I mean, this is normal, I can have a separate column with any name to work as a primary key, then I don’t need to hard code it, and I can perform my functions as usual, for example

 id,person_id ,book_id 

Answer 1

Composite keys should never be considered in "new" applications. They were used in the past by people who are used to thinking that “business keys” are better than “surrogate keys”.

Edit: As Chris said, I am expanding my answer.

Let me start by stating that I understand this question as “composite primary keys” and “surrogate keys”.

In addition, I acknowledge that there is one use case for a complex key: in crosstab lookup tables, also called “link tables”. They are used in many tables and consist of only two fields: both foreign keys, which form the primary key for the xref table. For example, the UserRole table contains user_id and role_id , nothing more. There is no class representation in Java, for example, for a table like this. This is usually @ManyToMany , with Collection on both sides.

I shared my views on the Natural Keys and Surrogate Keys keys in another answer ( Hibernate: views in Composite PK vs Surrogate PK ), and I believe that composite keys share some of the disadvantages of the Natural Key, without bringing any real benefits.

The problem with compound keys is that you need two values ​​to uniquely identify the record. This becomes a problem when you start having tables that reference records in this first table. The second table requires two columns in order to be able to refer to one record. And if this second table uses a composite key consisting of one value + foreign key, now you have three columns to uniquely identify one record. And the third table will require these three additional columns only to refer to one record in the second table. This is actually a snow globe.

Another disadvantage is that the requirements are changing. All the time. So, what today seems like a good composite key is not a key today. That is why we have surrogate keys: to be future.

Composite keys are mainly used to make entries in a table unique based on a set of columns. For example, if you have a Customers table, you can have NationalId + Country as a unique value, which means that two users cannot use the same SSN if their country is USA. But it is possible to have the same number for two records if they are not in the same country. If you like compound keys, this would be a good candidate for this. But, as I hinted earlier, you can use a surrogate key and apply a unique constraint. You will have the benefits of a composite key plus surrogate key security.

Answer 2

I cannot think of any conditions under which you NEED should use a compound key. Some Pro arguments using a single id column include:
1. better indexing
2. simpler joins
3. simplify guis design
4. the fact that most ORMs work better with a single-field PC (unfortunately)
5. easier to delete entries

In your case, although you can have a composite / surrogate key for person_id and book_id , and this will be very useful, you can also have one identifier column, which CAN will be your main key too, but this is not necessary. You can use person_id and book_id as PK or just an index, as well as for id columns. The id column makes your life easier when you delete material or select individual columns to view. With today's RDBMS, where you usually do not need to worry about the size of the table, it is advisable to include one column - it is preferable to automatically increase the identifier columns of all your tables just in case you need it. I believe that this will not hurt you in any way.

Answer 3

If you maintain a relationship between a person and books that are individual (for example, maybe you are using a website where users can rate books they read on a scale of 1 to 5), then the composite primary key in the votes person_id table and book_id makes the same sense, if not more, as having a generated identifier and a unique index in combination (person_id, book_id) . The combination of man and book defines voice recording.

Answer 4

Surrogate keys are internally bad and should be avoided at all costs. They make no sense in the real world. But sometimes they are necessary.

Leaving this aside for the moment, your example shows exactly why composite keys are needed - more than one person can have a copy of a particular book - and a person can have more than one book - this is an N: M relationship. And representing it in a relational database is simple : You put another table in the middle with the book PC and the person PC.

id, person_id, book_id

But (if you do not want to use a scenario where you need to distinguish between 2 copies of the same book belonging to the same person, in this case, the scheme requires several other changes), since the combination of person_id and book_id is already unique, why do you need you need another unique identifier that is not related to the data you are trying to simulate.