May 1998                   Issue: 2

Journal of Conceptual Modeling
www.inconcept.com/jcm

Common Model Fragments:
People and Organizations: Part Two
by Scot A. Becker

What? Not again….

I know, I know, I covered this last month, right? Well, not good enough.

Besides, although I had intended to write about products this month (In fact, I have the data model roughed in) I am too busy to complete it for this month. Given my travels and the upcoming Visio conference, I just don't have time to finish it for this issue. We'll try again next month.

Excuses, Excuses

I don't know which is worse, my bad model or that fact that none of you called me on it (save one person). I'd like to offer some reasons as to why I'm not happy with last month's edition. First of all, I was torn between two approaches for this column. My initial aim was to use common models to educate our readers on ORM; a kind of a learn by example approach. So my initial intent was to keep the models as simple as possible for the first couple of issues and gradually rise them up to the level that I would actually model for a customer. This, however, raises a couple of problems. The first is in making a "sloppy" model, I'm not doing anyone (who wants to make better models) any favors. The second problem is that I am trying to increase the use of ORM and sell the method a bit. If people look at an over simplified model, they may not think ORM is worthwhile and they will continue to use bad methods and screw up their databases. And, lastly, since these models feed off of each other a bit, I can't over-simplify the first model without sacrificing the later models (which are supposed to be more complete). Thus, I am going to take a more rigorous approach in the future

The other reason I didn't like last month's edition if the fact that I was rushed and screwed a few things up. Publishing this journal is a fairly time consuming process, and I simply ran out of time last month to "do it right". If this continues to be a problem, perhaps I will publish these things on a bi-monthly basis or something.

So this month, I will point out some errors, corrections, and enhancements to last month's edition. For those of you who thought my last model was fine, please read on. You may want to refer to the original article as well.

Plain Ol' Stupid Mistakes

The first mistake I made was in the following fragment:

One way to verbalize this fact is to say that every Contact has one or more Contact Notes, which is true. However, it also says that every Contact Note has one or more Contacts, which is not true. Remember, in the last article, when I said that not including fact instances was going to bite me? Well, it did. This error never would have happened had I included a valid and complete set of fact instances to check the uniqueness constraint. In a way, I am glad I did this, I can think of no better way to prove to you that fact instances are extremely important. Learn from my mistake.

The uniqueness constraint should span only one column, as follows:

Now, in talking about that last error to an ORM proficient person (to protect his identity, I'll call him Harry Telpin), he discovered another goof up, below:

This says, simply, that every Party Type can have at most one Party; which is ridiculous. However, in all fairness to myself, this is analogous to a typo as I did have this right the first time. However, when I was moving stuff around to make the picture pretty, this role got reversed. It should be as follows:

Proper Reference Modes

The next problem I want to discuss is related to my initial desire to keep the model as simple as possible. I left out a lot of look up tables that would normally be included. You can see this by my inclusion of the "Name" reference mode throughout the model. Let's take the above figure as an example. Normally, one would probably not identify a party type by its name, but rather some sort of an ID that identifies a name. For example:

This "error" is frequent throughout my last version of this model. In the future, I think I will make the models I show here more like "the real world" in this regard.

Addresses

The next thing to discuss is how I implemented addresses (below).

While this is essentially correct, we are missing a few dependencies. Some have suggested that a city (in a state) is identified by a zip code. While this is usually the case, it is more correct to say that a post office (in a city) is identified by a zip code. For example in my town, Ramsey, MN, we are too small to have a post office, so my Zip Code, 55303, actually identifies the post office in Anoka, MN. Often, in giving my address information to someone's system, my zip code will miss identify the town I live in as Anoka (or, in many cases, nearby Minneapolis). This leaves me to believe that listing my actual city/town on the envelope is meaningless and only mandated by the Post Master General as a sick sadistic joke: but I digress….

One way to model this situation is:

Note I said, "one way". This would limit your list of cities to those who have post offices. There are lots of variations you could make to this fragment. For example, you could identify address lines via a unique ID so that you can reference them in a table. For example, many people may live on "123 Able Street" through the world. If you wanted to, you could store those separately, or leave them as a value, as I did above. Likewise, you could also store city codes and do a similar thing. You could also make city an independent object type so that you have a master look up table somewhere of every city and it's corresponding post office (which is in a city which is in a state, see the recursion?). You can get even more specific and note that the "new" nine digit postal code can identify a street. Etc, etc.

I am going to leave this up to you, as it really depends on what you are trying to do. In my model, I was being sloppy, but I was emulating how a package like Outlook might store addresses. I doubt it has any internal correlation between zip codes and cities.

On another note, I can think of no better example of one data element containing 'hidden' information than a postal code. From here, you can really derive a city (more or less), a state, and possible even a street/block. Even thought all this information is available, I still can't get my mail in a timely fashion. Go figure.

Next Month

I hope the following month will settle down a bit for me so that I can finish my next installment of Common Model Fragments: Products.

Scot A. Becker is a software consultant and the founder of Orthogonal Software Corporation. He is also a certified ORM consultant and trainer, a certified Visio trainer, and former Editor of the Journal of Conceptual Modeling.  

Contact Information:

Scot A. Becker
Orthogonal Software Corporation
scot@orthogonalsoftware.com
www.orthogonalsoftware.com

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