May 1998                   Issue: 2

Journal of Conceptual Modeling
www.inconcept.com/jcm

Composite Objects in Relational and Object Relational Constructs Using InfoModeler 3.1 (Part Two)
by Patrick Hallock

In part one we were looking at how a relational model can be changed to an object relational model with the addition of name row type information. This time we are going to show how to create nested objects using several named row types. The address model has changed to reflect a different world. In last month's article the simple approach was taken. An address is an address and you just have to enter the whole thing in without any modeled way to help keep things straight. For example, Lake Elmo with zip code 55042 is valid while Lake Elmo with Zip code 55041 is not valid. Lake Elmo has only one valid Zip code. This problem exists in many production systems.

This month several changes have been made to the model. The changes start with identifying a CITY. CITY is identified by the unique combination of CITY NAME and STATE CODE. (Leaving out state name for now). A ZIP code is for exactly one CITY [CITY NAME, STATE CODE].

Some examples may help out:

Now an address has at least an address line and a zip code. The city and state do not have to be repeated for every entry in the database. This is a huge improvement. It also allows the data entry person to enter the address line and zip code only. This is a time and space saving improvement. Data accuracy is also greatly improved. There is nothing worse than correcting old system's addresses. In Minneapolis, we encounter the city names "Minneapolis", "MLPS", "Mineapolis" and others. The address was also given an ID in this model. this new model is shown in Figure 1.

Figure 1: Newer Conceptual Model

As would be expected, we get a new relational model (Figure 2).

Figure 2: New Relational Model

The Zip table is closer to what business systems use. Since the Address table no longer needs the city and state information, it is also smaller.

The object relational model requires some changes to the conceptual model. Named row types are created for City, Zip and Address. Try this without making each one a named row type. Note the interesting errors. Remember, we are trying to nest object types. If you forgot how to do a named row type, refer to the first article.

Figure 3: New Object Relational Model

In Figure 3, we see the nesting, which is not shown in the relational model (Figure 2).

Also, note that it has all collapsed into a single table and that the ORACLE 8 script would contain 'CREATE OBJECT TYPE(s)' for ADDRESS, ZIP, and CITY.

But, we lost the good features of the relational model. Maybe this is not a good implementation model, But, it shows the nesting of objects and the consequences of our changes. Merely changing the "named row type" information may not result is a desirable end result.

There are two lessons here:

1. Yes, you can nest named row types.

2. Is that what you want? As tempting, as these simple changes in the model may seem, it can lead to undesired results.

Address models are an interesting problem. Is this the best model? Probably not! In doing this article to show how object relational mapping works, I have discovered many other interesting problems.

Here are a few:

• It does not cover international issues Zip Code Vs Postal Code

• Different formats

• Data quality

• Some Zip Code - City - State mapping sources do not correctly make the relationships. For example, Asking for a Zip Code of a [City, State] gives Zip Code(1). Asking for the City, State for Zip Code(1) does not return the same results. You may get back only the primary postal city for the area and not the specific suburb.

• This problem exists on marketed CDs, Internet search engines and data from the US Post Office. Good news is, many stores have it right. Note, which ones do as you make purchases. I got one store to make several entries for me so I could watch it work.

• Some Zip Codes refer to a business, such as 3M, which is in a CITY. It identifies both?

• Some Zip Codes refer to a military installations

• Some companies have more than one Zip Code, in the same CITY.

• Some states have the same city name more than once. (I think Kentucky or Tennessee has 16 or so cities with the same name - I got this from the Click and Clack brothers Saturday AM automotive show on public radio, so it may not be true….)

• If you are lucky you data source has a type code included, so you can tell if the entry is for a company, military or obsolete, etc.

And you thought this was easy! Actually it is one of the most hotly discussed issues in most models. They argue about extra tables, bad data, extra data entry, and enforcement at the application level versus enforcement within the DBMS.

In order to get a discussion going, why not make your suggestions (maybe just part of the issue, or alternatives for one part of the model)? There may be several good models based on the application's requirements, or there may be a very good meta-model that works in both complex and simple application requirements. Models do not need to be shown using ORM graphics, plain (structured) language works for me.

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