Re: Modeling Address using Relational Theory

From: dawn <dawnwolthuis_at_gmail.com>
Date: 31 Aug 2005 14:59:06 -0700
Message-ID: <1125525546.555367.27220_at_g14g2000cwa.googlegroups.com>


David Cressey wrote:
> "dawn" <dawnwolthuis_at_gmail.com> wrote in message
> news:1125428483.999833.250400_at_g43g2000cwa.googlegroups.com...
> > Marshall Spight wrote:
> > > Gene Wirchenko wrote:
> > > > On 29 Aug 2005 20:49:44 -0700, "dawn" <dawnwolthuis_at_gmail.com> wrote:
> > > > >
> > > > >I understand the Line1, Line2, Line3 implementation, but any model
> with
> > > > >those attributes screams "this model is not relational", right? I am
> > > >
> > > > No. It is not 1NF if the attributes can have other than one
> > > > value, but the names are irrelevant except that they be unique within
> > > > the relation.
> > >
> > > Agreed. Line1 and Line2 are not two interchangable instances of
> > > the same kind of thing; they are specific distinct fields. It
> > > is not a repeating group.
> >
> > I realize it is not a repeating group, but I thought that having
> > Major1, Major2, Major3 or AddressLine1, AddressLine2, AddressLine3
> > violated relational modeling principles. Are you saying that this is
> > just fine? If I understand correctly, it is fine because line1 is the
> > first line, where line2 is the second line, so they are different
> > attributes? So, if you want to have a person's name and up to 4 of
> > their former names, does it violate relational principles to have these
> > as attributres Name, FormerName1, FormerName2, FormerName3,
> > FormerName4? The most recent former name is decidedly a different type
> > than the one before that. If relational theory supports lists made in
> > this fashion, I find that really fascinating, don't you?

>

> A long, long time ago, I asked you whether a pizza with onions and mushrooms
> is the same thing as a pizza with mushrooms and onions. If you had thought
> about the implications of that question, you would have the answer to the
> question that is before you now.
>

> Major1, Major2, Major3 are just names. What's in a name? The question is
> whether or not they form a set of majors, at the rock bottom semantic level.
> Let's take Course1, Course2, Course3, because it's easier to see that they
> are a set, and not a list.

In fact, even I would say this constitutes a set and not a list. Major1, Major2, and Major3, however typically indicates that the "first major" is Major1. In other words, the modeler embedded an ordering to this data in the names of the attributes (!) as is also the case with addressLine1, addressLine2, addressLine3.

>

> At the rock bottom level, a student enrolled in Psych101, Calculus101,
> English101, and Programming101 is still enrolled in the same courses if one
> permutes the order.

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

> Disguising this set as attributes (columns) with
> different names is obfuscating the fact that they are a set.

Do you agree now that I am talking about ordered lists, whether or not they are sets?

> That obfuscation is going to come back to bite you when you make queries
> that should yield the same result regardless of permutation in the set of
> courses a student is enrolled in.

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, although I realize that the data from the attributes is rarely parsed out to get this ordering -- it is simply understood from the names so that queries are done properly.

> By creating a separate relation,
> listing Student_id and Course_id, you make it trivial to make those queries
> invariant to permutation of order in the set.

Absolute agreement on that. I have never thought it to be a good modeling technique, even if oft used, to put 1, 2, 3 at the ends of attribute names and when you see those, whether or not the attributes should have a logical order to them, it is an indication of poor modeling or a compromise in the implementation.

> And that's the real point behind normalization. It isn't some mumbo jumbo
> you have to go through in order to satisfy the Spanish inquisition about the
> orthodoxy of your faith. It's a way of preventing certain problems that
> will come up at access time or at update time if you depart from the normal
> forms.

Yes, so what is a good way to model the
non-city/state-province/postcode/country address lines?

> For each normal form, there is a clear outline of the access
> anomalies or the update anomalies you will encounter if you depart from that
> form.

>
>

> If you are willing to deal with those problems, departure from a given level
> of normalization isn't necessarily "bad design".

agreed, but it is good to start with a clean model and then identify reasons to deviate for the implementation, right? I'm looking for a good relational model for addresses. I did get one from someone, but one that models a bunch of attributes that people rarely care about capturing and can parse out if needed (such as street number).

Do you understand what I'm looking for with a relational model for address that is considered both relational and at the right level of detail for the typical problem domain? --dawn

>

> It's they people who don't understand the consequences of departure from the
> forms, and then keep moaning "why do these things keep happening to me?"
> whenever they build a relational database, that are creating bad designs.
Received on Wed Aug 31 2005 - 23:59:06 CEST

Original text of this message