Re: Modeling Address using Relational Theory

"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.

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.

>
> > 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.

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. Received on Thu Sep 01 2005 - 14:57:58 CEST