Another Database Design Mistake to Avoid
In another post, I mentioned that as a rule of thumb, if data has a 1:1 relationship, all the fields should be in the same table.
Of course, with many rules, you can go overboard. Blindly following rules is the mark of a beginner. Search for:
The Dreyfus Model of Skills Acquisition
Here is a caveat.
From a DBA standpoint, if the table has large objects in it, such as BLOB (Binary Large Object), CLOB (Character Large Object) or record objects, this is when I would consider splitting these into a separate table and even a separate tablespace. This is in spite of the fact that under the covers, Oracle does move big objects into their own table.
Once I went on an interview and was told about the data design. A java programmer insisted putting a large object into each row in the main customer table. This was to be used for online test results, and he wanted it object-like for his java programs.
It should have been obvious that the customer data was identification data, and the online test would be transaction data. Even if it was a 1:1 relationship, intuitively, the test should be separated into another table. What if the company decided to offer a new series of multiple online tests for their existing customers? Of course the existing data model would not accommodate such a scenario.
A big problem had arisen. For every row created, a 4 kilobyte LOB was created also. Most of these LOBs were in fact never used. Most of the database was empty. Over 90% empty.
As the database grew in size (a terabyte I seem to recall), the whole system got slower and slower. And so, in DBA fashion, they decided to implement Real Application Clusters to handle the work load. A more complex and expensive option.
Solution:
My take was that they should have changed the database design. Instead of trying to fix the Leaning Tower Of Pisa. Only create a new row with a LOB if a test was actually taken. The existing hardware would probably have worked fine. Hire a data architect who understands database design. And a java programmer who knows SQL well enough to insert a new row, and do table joins.