Are there too many foreign keys?
I am using MySQL and planning the database structure for the system I am creating. As I walked along, I started to wonder if it is acceptable to have a foreign key constraint on many different tables. As far as I understand, that would be nice as it makes sense. But I would like to double check.
For example, I have a users table and I use user_id as a foreign key for many tables, sometimes multiple times in the same table. For example, I have a relationship one-to-one
with a table user_settings
that naturally stores user_id
. And then I have a companies table that has multiple key references user_id
. In this case, I have a column that tracks the user who created the company in the system ( created_by
), a column for the main contact ( main_contact
who is also a user of the system), and there might be another link there. Thus, already one key is user_id
used as a foreign key constraint 3-4 times.
Just to add some more information, I have a task table and of course you need to refer to user_id
to keep track of who it is assigned to, and I also have another column that keeps track of the user who created the task. It will be assigned_to
and created_by
accordingly.
There are more tables, although this link goes back to this key. I could have up to 8 links already. I do believe I have designed it properly so far, but based on what I mentioned, does that sound great?
source to share
Using a foreign key seems fine to me - you are simply representing logical relationships between tables.
The user on your system interacts with data in many different ways, and your approach is correct to define these relationships.
The key point, which I think is that under great circumstances, you won't always want (or need to) do all the joins that represent your relationship - just the ones you need in that context.
source to share
Consistent with my lack of how you define it is great to use the user id in many tables as a foreign key.
If your row is: I have a companies table , which itself has multiple user_id key references does not mean that you are using multipe user_id on the same table, and I know you are not.
source to share