More Database Design Mistakes to Avoid
There is more than one way to split a 1:1 relationship into multiple tables. In the first example, the table was split horizontally, and different columns went into different tables. But the number of rows stayed the same in all of them.
The stranger way is to split the table vertically.
In other words, to have duplicate tables!
At one shop they stored error messages in lookup tables. I looked at these tables. Note the plural, tables. Not table. It turns out that there was not one, but two error message tables.
Both tables had EXACTLY the same table structure! The same fields, and the same primary key! I looked at the data. All the rows in Error_Lookup_TableA had a PK value less than or equal to 10000. And all the rows in Error_Lookup_TableB had a PK value more than 10000. There was no overlap of values. And the combined number of rows were only a few thousand, so it’s difficult to believe it was somehow split up for “performance” reasons. And this was a shop that did not use partitions.
In order to make the thing work, there was actually code to select which table to use!
If ( Error_code <= 10000) then
from Error_Lookup_TableA a
where a.pk = PK_value
if (Error_code > 10000) then
from Error_Lookup_TableB b
where b.pk = PK_value
Merge the two tables into one table. All that is needed is to insert the values from the second table. Then correct your code. It’s easy enough to see where the second table is referenced using All_Dependencies. Drop the second table.
Keep the values unique, by using the primary key. That’s what primary keys do.
Someone will invariably say that you could also create a view to marry the two together. But why add yet another object, and more complexity, to the complexity? While there may be some benefits, why not just fix the underlying problem?
Honestly, how can you even -think- of creating two duplicate tables with exactly the same table structure?