More Database Design Mistakes to Avoid
I’ve seen a few “designs” where a number of 1:1 relationships were actually split up into a number of tables.
The tables of one design (in Oracle 7) were key tables in the system. Now a table join was required for almost every subsequent query. Querying on a single table would take 10 to 15 seconds. After joining to the second table to get the few more fields, the response time slowed down to about 45 to 60 seconds! About 4 times as long.
Table joins can be really expensive. Much of Oracle’s optimizer is concerned with the optimization of table joins.
As a standard rule of thumb, the fewer table joins, the faster the query. This is why in data warehousing, there is the concept and practice of denormalization.
This also required a certain amount of people time to add the second table, and create the join condition. I wondered, how many hours of people time and dollar value did this add up to, over the lifespan of the system? And what was saved? Perhaps a few minutes of thinking time.
Possible causes for such a “design”:
– “let’s make a table” in response to every request for a new field in the database.
– no analysis of the existing table structures to see if another table with the same PK already exists.
– Speculation that splitting a single table up into multiple tables will give big improvements in “performance”, but failing to consider table joins, etc.
Some believe that by eliminating a few VARCHAR and NUMBER fields from key tables that there will be big increases in performance. But so far, I have only seen this as a speculative theory. I have never seen any structured time tests against realistic rows of data, and work load scenarios, where the only independent variable was the two different data models.
I’m doubtful that there would be any significant savings in space, or increases in speed. Perhaps I’ll eventually get around to some actual tests of my own. If someone has already done some tests, please send a link to the results.
When the primary key of the proposed new table is the same one as an existing table, just add the fields to the table with the same primary key. The design will be much more intuitive, and you won’t need to do any more table joins.
If you really want a number of different objects, consider a single table, and using views with subsets of the fields.