Merging tables with a 1 to 1 relationship in a database

Question

Is it a good idea to merge two tables that have a 1..1 relationship together?

I have inherited a database with too many tables linked together in a 1..1 relationship. I'd like to evaluate the benefits of merging these tables where possible to have more tidy model with higher query performance.

For example table t1 has 150 records and table t2 has 150 records, they each contain 5 columns and these two tables have a 1..1 relationship. Would it be a good idea to merge them into one table, say t3 which contains all the columns from t1 and t2?

Also does it make any difference if these tables are reference tables (less than 200 records) or an operational tables?

Any idea is appreciated.

Answer 1

If they are small reference tables then I would be more inclined to merge them than if they are not. If reference tables, then they are likely separated due to over the top normalisation and merging them makes séance from query performance perspective.

If they were large fact tables I would be less inclined to wade in before checking ramifications, as it could be that they were separated onto separate file groups with files on different spindles for parallel operations. If this is the case however, I would still be inclined to merge them, and consider table partitioning.

Either way, be sure to thoroughly check for Database and client application dependencies before Changing the table structure.

Answer 2

If they are truly, really, 1..1 relationships, then it must be the case that both tables always get inserted to and deleted from in the same transaction. Because otherwise there would necessarily be some moment in time when there is some row in one of the tables with the 'corresponding' row still missing, in which case that would prove that the relationship is not really, truly, genuinely, 1..1

Logically speaking, it might be beneficial to have them unified. But consider that there might also be security reasons for splitting the information (one user can see only one half, other users can see only other half, for example).

And of course you'll have to be careful with the impact this operation will have on your currently operational system. In ages long gone, estimating this impact was typically dead easy (one catalog query and you had it). Nowadays, it is typically as good as impossible.

Answer 3

These two tables may both have 150 records right now, but is/will that always the case, now or at some time in the future?

Are records first created in one table -- the absence of records in the second table indicating some kind of status -- and only later added to the second table -- to indicate resolution of the initial status?

Are the two tables for completely different purposes -- two logically different sub-systems?

These are just some scenarios where having separate tables may be appropriate.

I can only "Amen" Pete Carter's comment: Be sure to thoroughly check for Database and client application dependencies before Changing the table structure.