Re: Modeling Address using Relational Theory

From: David Cressey <david.cressey_at_earthlink.net>
Date: Wed, 31 Aug 2005 09:38:01 GMT
Message-ID: <ZTeRe.4529$9i4.1471@newsread2.news.atl.earthlink.net>

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

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. Disguising this set as attributes (columns) with different names is obfuscating the fact that they are a set.

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

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

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 - 04:38:01 CDT