in reply to OT: benefits of database normalization

Cannot see any problem with design 1. Just base on what you gave, I have to say that it is normailized.

It would become not normailized, for example, if you also have type description in this same table.

Ideally, and I guess design 1 does, you shall have another table called type, which stores things like type id, type description etc. And the type column of the fact table shall be a foreign key points to the type number column of type table.

I don't like design 2.

  • Comment on Re: OT: benefits of database normalization

Replies are listed 'Best First'.
Re^2: OT: benefits of database normalization
by revdiablo (Prior) on Oct 03, 2004 at 02:23 UTC
    Cannot see any problem with design 1.

    You don't think it's wrong to mix two different things into one table? Or are you of the opinion that Aliases and Facts are two "types" of the same thing?

    I have to say that it is normailized.

    Surely one column's meaning changing depending on the value of another is a violation of some rule of normalization? Like I said, I don't know the names of all the rules, and what they mean exactly, but this seems wrong.

    I don't like design 2.

    Any particular reasons?

[reply]

      My understanding was based on the name of the columns: "type". I don't deny that there is a chance of misunderstanding. Column type might mean something that really not sort of "type". To really appreciate whether design 1 is actually normalized, I have to judge base on the meaning of the true data, which you didn't really specify in your original post. (It would be much easy if we are in front of a whiteboard now ;-).

      To clarify, what I meant was: (I have to use something that is more meaningful to everyone, something that is part of everyone's daily life, and yet describes my understanding of your design 1. ProductOf is my understanding of your "type".)

      FruitProductOfTaste
      Apple11Some Taste
      Apple22Some Taste
      Orange11Some Taste
      country codecountry name
      1US
      2Canada

      This is normalized, but will be not normalized, if:

      FruitProductOfcountry nameTaste
      Apple11USSome Taste
      Apple22CanadaSome Taste
      Orange11USSome Taste
[reply]
        My understanding was based on the name of the columns.

        I thought they would be self-evident, but on reflection, I probably should not have assumed as much. I have a bad habit of doing this. I just get wrapped up in the problem, and don't think about it from an outside perspective. I have updated the original post. Hopefully the further explanation is helpful.

        To clarify, what I meant was . . .

        Indeed, this appears to be a nicely normalized design. I can understand what you were thinking when looking at my bad examples. This isn't quite the situation I was discussing, though. I hinted at that when I said The data column's meaning changes depending on the value of the type column, but I can see how my post could have been misunderstood. Let me know if the update explains things better, or if there is still something missing.

[reply]

In Section Seekers of Perl Wisdom