Re: Table normalization

From: Roger Crowley <rcrowley-enkidu_at_learningframework.com>
Date: Wed, 24 Jul 2002 16:04:21 GMT
Message-ID: <3D3ED005.6050607@learningframework.com>

Stjepan,

I think the reason there aren't many rules of thumb in this field is because there are so many other variables. For example, you mentioned separating "Street" from other data ... and Kenneth gave some very good reasons why that's not normally done. However, I once designed a newspaper circulation system and the database had to be able to "map" out a route for each newspaper carrier. That meant using street as a separate "entity" (though that's too generalized of a term for what it ended up being) in a 1:M relationship to households (subscribers). But that was an extremely (read small) subset of all possible streets (and we didn't have to worry about streets crossing city boundaries and such, because of our limited view - carrier routes). Though the newspaper would have liked to have global subscribers, it wasn't USA Today or the London Times (which I see people reading over here on this side of the pond).

So the bottom line is that every db designer has to balance all the variables before deciding on a particular db model. Sometimes the pluses outweigh the minuses. Sometimes you have to throw away the book because you know the developers will have a devil of a time navigating through the physical model, based on your "perfect" logical model.

Another thing I've learned is that you almost never get it right the first time. After your design has been in production for a while, you get a better sense of what kind of data accumulates in your db ... and then you find, after a couple of years ... if you only knew back two years ago what you know now, you'd have done it differently. <g>

Roger Crowley - DBA - LearningFramework

Stjepan Brbot wrote:

> Thanks Kenneth,
> 
> of course that there is no exact and general threshold for triggering
> the process of normalization (table decomposition). Also, I do not
> expect the foolproof recipe and I do know that many of the skills in
> database design are won by expirience.
> 
> At this moment I do not have big expirience with DB design but do not
> want to regret for newbie mistakes in future. So, I'd like to hear
> something usefull from other expirienced DB designers how they cope with
> such a issues (like normalization is) in their praxis.
> 
> --
> 
> Stjepan Brbot
> 
> 
> <Kenneth Koenraadt> wrote in message
> news:3d3e8493.12710967_at_news.capgemini.se...
> 

>>Hi Stjepan,
>>
>>There is no general "treshold", the question of normalizing depends
>>of what is possible and necessary.
>>
>>Putting "Street" in a separate table would be problematic in a number
>>of ways :
>>
>>1) Street names are not unique. Same street name appears in different
>>towns.
>>
>>2) Street names can be spelt differently.
>>
>>3) New streets come up and old streets disappear frequently.
>>
>>4) You will probably never get a full list of all streets in the
>>world or even in your country, unless you are the national Mail
>>Company . Thus your street reference table will never be up to date,
>>so you will never really be able to use it for anything.
>>
>>So a "street" reference table will probably never be useful for you.
>>On the other hand, a ZIP code reference table would be obvious to
>>implement. There is a limited and well-defined set of ZIP codes, they
>>are easy to achieve, and you would probably want to validate your
>>customers ZIP codes.
>>
>>- Kenneth Koenraadt

> 
> 
> 

Received on Wed Jul 24 2002 - 11:04:21 CDT