Should I make a composite primary key?
I study this question, there is such a phrase in the answer
Composite primary keys typically arise when mapping from legacy databases when the database key is comprised of several columns.
Do I understand correctly that legacy is practically a synonym for the word "obsolete"?
If so, is it worth it, when developing a new application and a new database, to make composite keys? Or just add another column, which is essentially nothing won't do it, because all the work with the database is usually done by frameworks?
3 answers
This is quite a holivar question, and there is no single correct answer here. But most often, opinions agree that the keys should not be composite, and they should not store any information about the entity. That is, it must be some (usually an integer automatically incremented) field that ensures the uniqueness of the record
There is a somewhat outdated approach in DB design, sometimes erroneously called "academic", where the primary key of a relationship is chosen as one of the natural keys of the relationship. This key is usually the name of the record.
In the question you have given, this is exactly the approach used. But this approach is outdated for many reasons. First: the primary key of a record is what is used to refer to the record both in the database itself and outside of it. It is advisable to do it as as little as possible - yet most natural keys are string keys.
The second reason is that many things that look like natural keys at first glance are not really so. For example, among people there are complete namesakes (and even those born on the same day). A store can have two products with the same name in different departments...
The third reason is that the natural key can also change, which also leads to problems. So, for a person, a natural key could be there may be a passport number or a birth certificate-but these documents can be replaced when you reach a certain age or in case of loss.
Therefore, it is a good practice to introduce a surrogate key - this is a new field that has nothing to do with the subject area. Usually its type is an auto-incremented 32-bit integer, less often - a 64-bit integer. It can also be a UUID.
As a rule, if there is such a field, there is no sense in composite keys.
However, sometimes composite keys are useful. These are the cases:
Link tables for many-to-many relationships. These tables are usually created and managed by the ORM libraries themselves-but sometimes you have to create them separately. In such tables, the natural key is the entire record - and there is no reason to create a separate surrogate key.
Some child subobjects that don't make sense in isolation from the parent one. If we are making a system for testing students, then sometimes it makes sense to refer to the answer to the question as the 5th answer to the 147th question - and not to the 3423rd answer at all. And sometimes the opposite.
Using relationships in the database for additional integrity checks. There is a so-called domain-key normal form, in which any restrictions on data are implemented in the format of foreign keys. In this case, sometimes it makes sense to include additional fields in primary key.
I believe the translation is in the sense of: "Composite primary keys usually occur when mapping to existing database tables, when a table key consists of multiple columns."
legacy here is practically synonymous with the word "existing".
When developing a new table, theoretically, you should not create information redundancy with a new key field if the existing combination of foreign keys(or other fields) in it is already a unique key.
But practically search by an additional unique key field may be faster or subjectively easier for the developer and thus make sense to achieve the desired result :)