MySQL: Where should I place the foreign key?

Question

I have a doubt. Please read the below example carefully.

1. There is an Income type called Other Income
2. Every single Other Income May or May Not be subjected to VAT.
3. One Other Income can have only one VAT, if it has any.

As you can read in above points, the relation between the Other Income and VAT is One to One/ None.

Now see the below image.

We wanted the VAT table to contain the Other_Income reference, so the idVat is in Other_Income table.. In case you are wondering why there is a separate VAT table, that is because lot of other types of fees are subjected to VAT too.

However imagine due to some reason someone wanted the Other_Income table to contain the VAT reference. In this case, If you are using MySQL Work bench or GUI tool to build the diagram, the 1 in relationship line will be presented in the side of Other_Income table and the 0..1 will be presented in the side of VAT table.

However, whatever it is, it has the connection One to One/ None. So, there is no connection between Relationship and the location of the foreign key? In other words, there is no rule something like You must put the foreign key to X table of the relationship. Other table is prohibited. ?

As far as I can see and know there is no such rule, you can put the foreign key to any table in the relationship, it is just your preference and decision to make the things "work". However I would like to clear my doubt.

Update

The above is just an example to explain the question with more sense. Therefor, please do not make your answer depend on the "exact" above example.

Answer 1

The foreign key can be null. In some cases, you just don't know what the value of the key is. You can have a look at this link.

There is a link between relationship and location of the foreign key. If you just put the value of the foreign key to the table where the foreign key is not defined, it would be just a normal value, without all the benefits which having keys provides.

Answer 2

There is a foreign key (FK) from one table column referencing another when the value in the column in the referencing table always appears in the column of the referenced table and the referenced column is unique in the referenced table. (It is declared UNIQUE NOT NULL or PRIMARY KEY there.) Also, in SQL a referencing column can be NULL.

Since the referenced column is unique in the referenced table, a row in the referencing table either IS NOT NULL and matches exactly one row in the referenced table or IS NULL. And many rows in the referencing table might refer to the same row in the referenced table. But some rows in the referenced table might not be referenced by any row in the referencing table. So a NOT NULL referencing row goes to 1 referenced row and a nullable one to 0-or-1 referenced row. And a row is referenced by 0-or-many referencing rows. So a foreign key means a 0-or-many-to-(0-or-)1 situation.

Your 1/2/3 just says that there is a nullable FK from Other_Income to VAT. Ie 0-or-many-to-0-or-1. That does not justify having a 1 instead of the many and it doesn't justify having a FK from VAT to Other_Income. Ie it is not true that 1/2/3 means "the relation between the Other Income and VAT is One to One/ None".

If it happens that there are other constraints then you have not said so yet. Eg that there is also a FK in VAT referencing Other_Income. In that particular case each Other_Income has its own VAT if it has one. (And it has to also be that a VAT has its own Other_Income if it has one.) But in that case if a VAT always has a matching Other_Income we have the Other_Income-VAT 1:0-or-1 and if a VAT doesn't always have an Other_Income and so is nullable then we have Other_Income-VAT 0-or-1:0-or-1. (I now use ":" because there is a "to" in both directions.)

The only time you get to pick where to put a FK is when you have 1:1 and you leave one FK out. Ironically in the 1:1 case you could just put all the info into one table without FKs and that is the symmetric and non-redundant design rather than two tables. In the 0-or-1:0-or-1 case if we wanted to avoid using NULL then we would have no FKs in either table and add a third table with the two columns, each a FK, each a PK, enumerating paired values.

Answer 3

The Other Income can have a VAT. So that table must be given a column idVAT. For every record there will either be an entry or NULL.

So you have an {0-n}:{0-1} relation. Every VAT ("reduced rate", "normal rate", etc.) can be assigned to n Other Incomes (with n being zero or any number). And every Other Income can have 0 or 1 VAT.

In order not to be able to enter a non-existent idVAT in Other Incomes, you make this a foreign key to the VAT table.

If you want a real {1}:{0:1} relation between two tables, e.g. the VAT table doesn't contain the VAT level ("reduced rate", "normal rate", etc.), but an amount of money, then things get slightly more complicated.

Usually you wouldn't make this two tables then, but just one (the VAT amount would be a nullable column in Other Incomes). If you really need two tables, then you would give the VAT table the idOther_Income, because each VAT entry refers to one Other Income. So this time it is vice versa, but still you would use a foreign key to ensure data integrity. This time from VAT to Other Income, of course. You would also give VAT.idOther_Income a unique constraint, so there can only be one VAT per Other Income.

Answer 4

I hope I have understand you question correctly.
It seams you have different Fee tables(like Other-Income ...). Any row inside Fee tables may have a VAT record.

Conditions are:

• A VAT won't happen if there is no fee (correct me if I am wrong).
• A Fee needs to keep trace of its VAT(if happens).
• A VAT needs to keep trace of its Fee (must happen).
• A Fee can have only one VAT.

I will suggest to have a base table called Fee_Base (or simply Fee) as the base for other fees. This base table will contain shared properties of other fees like date of happen, amount etc.
Having Fee_Base foreign key being mandatory inside VAT will satisfy the 1,2 and 3 conditions.
Assigning Fee_Base foreign key being Unique will satisfy number 4.

Simply Fee_Base foreign is the primary key of VAT table.