Combining Identifying Data, Transaction Data, and LOBs in a Single Table


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:

Correction of Too Many Tables for a 1:1 Relationship, including a BLOB

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.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: