Re: Modeling Address using Relational Theory

David Cressey wrote:
> "Marshall Spight" <marshall.spight_at_gmail.com> wrote in message
> news:1125538652.912144.28340_at_z14g2000cwz.googlegroups.com...
> > dawn wrote:
> > >
> > > Absolutely agreed. However, the student with a Major1 of Biology and a
> > > Major2 of Theater is likely to walk at graduation with the Theater
> > > majors while if the order is reversed, they will walk with the Biology
> > > majors (this is, of course, dependent on the business rules of the
> > > institution, but the fact is that business rules might be set based on
> > > this ordering whose value is embedded in an attribute name).
> >
> > Well, I actually have two majors, and I can tell you that there
> > was no ordering between them in the "business rules" of the
> > university that I attended.
> >
>
> There's another example, beside multiple majors. This example crops up
> frequently in the health care business.
>
> It's "principal insurance policy" versus "supplemental insurance policy".
> These are frequently stored side by side in a single table. In almost all
> cases, the semantic difference between the two columns determines which
> column on wishes to use in a query's conditions.

Good example. In this one the ordering is not with numbers in the attribute names, but with ordering information in those names. If implementations where lists are permitted, this could be as a single attribute with cardinality of 2 (an occurs in COBOL using indexed sequential data structures, for example). I think this is an example of where the target environment makes a difference in the logical data model.

Unfortunately (my opinion), this also seems to make a difference in the conceptual data model. Those who have been trained only in the relational model push that thought process on SMEs, even in cases where the end-users might initially think in terms such as "then we have insurance policies, up to two of them, and we submit to the first and then only submit it to the second only after the first denies payment"

Even if we do an implementation in an RDBMS, it seems that the conceptual data model could be well-served with list structures.

>
> The semantics are that the primary insurance policy will be asked to pay for
> the procedure, and the suppemental policy will be consulted if, and only
> if, the primary insurer denies payment.
>
> Here's the two columns are drawn from the same domain, but have different
> semantics. It's not a violation of 1NF.

It is easier for me to see this as not violating 1NF. When there are numbers that have meaning in the attribute names, that just seems wrong to me when a list structure would be so much more elegant IMO.

>
> >
> > > Yes and that is one of my points. I was completely unaware that it was
> > > considered proper relational modeling to place ordering data in
> > > attribute names,
> >
> > It isn't. And that's not what's happening with addresses, either.
>
> It depends on what you are going to do with the data.

I do appreciate that perspective (not always shared by others).

> If you are generating mailing labels, you always put the recipient's name
> on the first line.
> then the two address lines, then the city, state, and zip on a single line.
>
> However there are unusual cases where you do a great deal of searching by
> street name. In such a case, you might want to model the data differently.
> Again, it depends on what you are going to do with the data.

It really is easy to put address lines in a list, even if only the lines that end up as 2nd & 3rd on an address label. So, the components that might be rendered as an address label are often (in non-SQL implementations):

MailToName (perhaps derived from title, first, middle, last, suffix, for example)
AddressLines (multi-valued)
City
StateOrProvince
Country
PostCode

Because AddressLines only has a cardinality of 2, it is not a big deal to make it two separate lines, but the only reason I see to do so is that the target environment has no list types. If the reason to do so is related to the target environment, then I would think the logical data model need to bother splitting out the AddressLines into two separate attributes and, surely the conceptual model need not do so, right? --dawn Received on Thu Sep 01 2005 - 15:51:47 CEST