Is it necessary to create foreign key in this query? [closed]

Posted on

Question :

I have the table called investors having as primary key the column id. I have the tables Users, Login_Logs and Accounts. All of these tables contains the foreign key investor_id. Between investor and user we have a one to one relation. between investor and login_logs a one to many relation and between investor and acccount a one to many relation as well. My question is in order to make a query to load easily info contained in the table users, login_logs and accounts do I need to store user id, accounts id and login_logs in the investors table? I mean do I need to create a foreign keys in the investors table?

Answer :

I am not entirely certain that I understand the semantics of your database design but foreign keys have a special meaning in a database.

A foregin key, investors_id in the Users table, is a constraint saying that if a value appears in the investors_id column in the Users table, then that value must appear once in the Investor table id column.

My interpretation of that fact is that a user might be an investor as well and if he/she isn’t then there must be a null value in the investors_id column in the Users table for that person.

This, in turn, means that as all investors must be users and then it is acceptable that there is the corresponding constraint the other way so you might have a foreign key in the investor table “pointing” at a record in the Useres table but it is not neccessary.

I would, however, think it over more than once. Do I really need to specifically point at this fact?

In the other two cases, foreign keys in the investors table referring to the Login_Logs and Accounts table, the same reasoning tells you that it is wrong.

As several Accounts table records may refer to the same investor in the investors table, there is from a semantic point of view no such constraint in the opposite direction.

The fact that you have a one-to-many relationship from investors to Accounts and from investors to Login_Logs makes it wrong. You would break the obvious semantics, an investor may deal with many accounts but an account is related to a single investor.

Finally, foreign keys might have performance aspects but I never use them that way. They are the glue that determines the semantics of a database and in my opinion that is the only way to use them.

So, my conclusion is that it is possible to have a foreign key in the investors table referring to the Accounts table but not neccessary and it is wrong in the other two cases.

Leave a Reply

Your email address will not be published. Required fields are marked *