Is it good to have several foreign keys in one table?

Question

Consider the following table structure:

titles(titleID*, titleName) platforms(platformID*, platformName) products(platformID*, titleID*, releaseDate) publishers(publisherID*, publisherName) developers(developerID*, developerName) products_publishers(platformID*, titleID*,publisherID*) products_developers(platformID*, titleID*, developerID*) 

Product table is a carpentry table using two foreign keys platformID and titleID. This is due to the fact that some headers have the same attributes on different platforms. To avoid repeating the data, I created a table of tables.

My question arises when I create the products_publishers table. Since a single product can have multiple publishers, I need to create a joiner table of three foreign keys. Is this the best practice? My script could create a table with four such elements. I considered using a column to store this data in a product table and abandoned the joiner table and the publisher table, but in an intuitive sense this does not seem to be correct.

Any thoughts?

Many thanks

Answer 1

This is quite normal and is called compound. Another common strategy I've seen is to also provide the table with a primary key (surrogate key), like all other tables. Some of the reasons are:

• a consistent data model in which each of your tables has a primary key
• it makes it easier and faster on one key compared to the composite keys connecting
• if you use a data model with ORM, it is usually better supported and makes your code easier and cleaner to deal with a single key than a multiple.

I personally think that the two composite keys are functional, but as soon as you have three or more, you should really consider introducing a surrogate key. Which strategy you choose, you just need to be consistent with it in your data model.

Answer 2

This is completely normal, and very common in practice. I think that every database I have ever used had such tables.

Answer 3

I would say yes, this is the best practice. Using an additional column in the product table will prevent you from having a product for multiple platforms.

What would you think about which fields should belong to the key in the joinery tables. If, for example, you can have only one publisher per platform and title, then the publisher ID should not be part of the products_publishers key.

Answer 4

I agree. In many projects, we use 2-3 foreign keys in some tables. It works great, and I cannot come up with a better solution for many relational problems - and I have never seen it.