I was recently involved in a discussion regarding the relationships between database tables and how they differ from the relationships between objects.

I should first say that I come to Platinum Solutions and this blog space not as a ‘java programmer’, but as an applications developer with a rich history (~25 years) of data-driven development. My only experience with Java until now has been with the late unlamented SilverStream, which lived prior to J2EE. More on this in a later entry…

The discussion centered around certain key pieces of data and the distribution of same within the database. Imagine data in a grandparent (GP) –parent (P) –child (C) relationship. The object model had relationships described between GP and P and between P and C, but no direct way for C to reach GP except through P. While this may be perfectly acceptable in an object model, this will be a problem in a relational database when you try to create a query that relates child to grandparent, and a real mess when you try to pull data from two children with the same grandparent but different parents.

For example:
The grandparent record is a list of crimes committed
The first parent record is victims of the crime
The second parent record is suspects of the crime

Let’s say the key in the ‘crime’ table is the state abbreviation and a sequential number; this is the ‘glue’ that holds the case together.

The key for the first parent (victim) is a sequential number. It has a foreign key back to the crime table. Ditto for the second (suspect) table. Each of these tables has a child table of addresses, as they may both be known criminals and give false addresses, or they may have a date element to reflect movement over time; whatever. The address tables have foreign keys to their respective parent tables.

Let’s say someone wants a list of cases and apartment numbers where the victim lived in a particular apartment building. If you have the case number in the address table, it’s relatively
simple:

SELECT CASE.CASE_NUMBER, VICTIM_ADDRESS.APARTMENT_NUMBER
FROM CASE, VICTIM_ADDRESS
WHERE VICTIM_ ADDRESS.STREET_ADDRESS = ‘1257 TENEMENT’
AND CASE.CASE_NUMBER = VICTIM_ADDRESS.CASE_NUMBER

without case number in the grandchild record, it gets more complicated:

SELECT CASE.CASE_NUMBER, VICTIM_ADDRESS.APARTMENT_NUMBER
FROM CASE, VICTIM, VICTIM_ADDRESS
WHERE VICTIM.ADDRESS = ‘1257 TENEMENT’
AND CASE.CASE_NUMBER = VICTIM.CASE_NUMBER
AND VICTIM.VICTIM_ID = VICTIM_ADDRESS.VICTIM_ID

Now let’s say someone sees the report and asks to include suspects living in the same building. The revised second query gets unwieldy rather quickly and I’m not going to try to reconstruct it. If you want cases where either (not just both) the suspect OR the victim lives in the building you have to modify the query to use outer joins. Good luck with the second query. If you can even get it to work, you can take a long lunch while it does the query on a database of any real size.

This is just the tip of the iceberg. The result set may need to add elements from other tables related to the case but unrelated to the tables discussed so far.

The moral of the story? Don’t let a pure object-related design go into development without the reality check of comparing the object model to the relational model to the use case matrix.