Converting a 1:M relationship to a 1:1 relationship

Question

We have a situation where table 'A' is a parent table and there are probably a dozen or so tables that are children of 'A'. In every case, there is a 1:M relationship between 'A' and the children tables.

It was decided yesterday that these dozen 1:M relationships should be converted to a 1:1 relationship. So, for each of the dozen child tables, the developer put a unique index on the FK column that linked the table back to 'A'. This was his way of enforcing a 1:1 relationship.

I suggested to him that what he perhaps should have done was to remove the FK column in each of the child tables and created a FK column in table 'A' that referenced each of the child tables. He questioned this approach because for each row in 'A', many of theses FK columns will be null because they are not always required.

What would be the desired approach in this situation?

Thanks - Randy

Answer 1

If I'm understanding you correctly there can only be one row in each child linked to a specific row in the parent table.

If this is the case, his approach seems better for the exact reasons he is mentioning. In the end it depends a lot on what you plan on doing with the data, but the best solution from a database modelling perspective is to have a FK column in each child table linked to the PK in the parent table.

Answer 2

In my opinion, your colleague/developer has proposed the most suitable solution.

From a Database Design perspective, it is common practice to create a non-clustered index on each foreign key column for a given table. Suppose you had 12 foreign keys, as you suggest, on the Parent table. You will be increasing the performance overhead of your insert operations into this table significantly.

By placing a single foreign key in each of the 12 Child tables that references the Parent table, you will be distributing your I/O load more efficiently.