June 1998                     Issue: 3

Journal of Conceptual Modeling
www.inconcept.com/jcm

Dealing with Embedded Objects
by Patrick Hallock

Modeling projects often have the problem of embedded objects. Embedded objects are those fields that have many sub-parts. The simple example is the field that contains two or more codes that are easily parsed. Example: BUILDING, FLOOR, ROOM:

Often the legacy query system has instructions that parse the CODE to perform select operations, such as all rooms in the Chemistry Building. This means some kind of substring operation:

SELECT…. Where substring(CODE,1,4) = ‘CHEM’;

Besides having the embedded code, we probably also have a performance problem. A small argument in this example is the CODE is probably indexed and the first 4 characters would be reasonable to find. Trying to find all buildings over 2 stories high removes that case.

By adding some information about building and rooms we create the following solution.

This creates the table:

It is only after solving this problem that the DBA and others explain to you, "You cannot do this because it will mess up all the references used in the other sub-systems." You complain, but to no avail. So, now what?

Here are 2 possible solutions. The first uses "derived and not stored" while the second provides both representations, one from the new system and one for the old system.

So what did you gain? Not much! You are back to the old code, but the conceptual level has entities that are derived from the Room Code. (The * symbol means derived and not stored). How are the other roles derived?

BUILDING = substring(ROOM,1,4)

FLOOR = substring(ROOM,5,2)

NUMBER = substring(ROOM,7,2)

This is merely a form of documentation. We are back to the substring functions in queries as well. Let's try for a better solution. In this version, we keep the original code then derive and store the hidden entities.

(The ** indicates derived and stored.) The (AK1) on the three fields indicates an alternate unique key consisting of these columns. The rules are the same, but now the values are stored in columns. The unique index keeps the population correct. This solution is also useful if the project requires you to have a single column unique key for all tables.

Interesting Note:

Notice that all the conceptual models remain very similar: the same objects, the same roles, and the same mandatory constraints. All that was done was some changes in the ROOM identified scheme and derivation rules (derive and store vs. derive and not stored).

Patrick Hallock is a Senior Partner and Principal Consultant for InConcept. He has over 20 years of ORM/NIAM experience and is  a certified ORM consultant, trainer/train the trainer and a certified Visio trainer.

Contact Information:

Patrick Hallock
President and Co-Founder
InConcept
8171 Hidden Bay Trail N
Lake Elmo, MN 55042
path@inconcept.com
(651) 777-8484
fax: (651) 777-9634
http://www.inconcept.com

© Copyright, 1998-2004 InConcept (Information Conceptual Modeling, Inc.) All Rights Reserved. Privacy Statement.
ISSN: 1533-3825