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

Question

Hello,

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*)  

The products table is a joiner table using two foreign keys of platformID and titleID. This is because some titles have the same attributes across different platforms. To avoid data repetition, I created the joiner table.

My question arises when I create a products_publishers table. Because one product can have several publishers, I have to create a joiner table made from three foreign keys. Is this best practice? My scenario could have me create a table with four such entries. I considered using a column to store this data in the products table and forgo the joiner table and publisher table, but intuitively this does not feel right.

Any thoughts?

Many thanks

Answer 1

It's perfectly OK, and very common in practice. I think every database I've ever used has had such tables.

Answer 2

I would say, yes, this is best practice, Using the additional column in the products table would prevent you having a product for several platforms.

What you could think about is which fields should belong to the key in the joiner tables. If e.g. you can only have one publisher per platform and title, then publisherID should not be part of the key of products_publishers.

Answer 3

Agreed. In many projects, we use 2-3 foreign keys within some tables. It works fine and I cannot think of a better solution for many relational issues - nor have I ever seen one.

Answer 4

This is quite normal and refer to as composite keys. Another common strategy I have seen is to also give the table a primary key (surrogate key) like all the other tables. Some of the reasons for doing:

• consistent data model where each of your table has a primary key
• it makesjoining easier and faster too on a single key compared to composite keys joining
• if you use the data model with ORM, it is usually better supported and make your code easier and cleaner to deal with single key than multiples.

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