Re: Design question

In article <933_at_6sigma.com>, clif_at_6sigma.com (Clif McLaurin) writes:
|> In article <2h1pve$a23_at_athos.cc.bellcore.com> derek_at_candyland.gamekeeper.bellcore.com (C. Derek Fields) writes:
|> #One area of discussion that is glaringly missing from any posts I have seen
|> #here or in comp.databases is a discussion of database design issues, particularly
|> #at the conceptual and logical level. Now that may be because these are so
|> #straightforward and simple that only dolts like me ever have questions, but I
|> #tend to doubt that premise, so I see one of two other possibilities:
|>
|> The design of even a moderately complex data model is NOT simple and
|> straightfoward, however, many believe that it is.

I completely agree. In fact, I will argue that the CORRECT design of a database (or data model to be more accurate) is more important in the long run that the implementation of the model. If the model is right, the implementation can be fixed. If the model is wrong, the best implementation won't correct it and fixing the model requires re-implementing it.

|> #
|> #I am designing a database for ordering phone service. Now this is a moderately
|> #complex thing to do, but at its simplest level, each phone service that I can
|> #order has two cans and string between them (remember your childhood, it hasn't
|> #changed all that much :-) ). Here is my question: If I model this, I get
|> #a 1-Many relationship between the string and the cans. However, I know that
|> #there are always exactly 2 cans, so I can end up with a reasonable, denormalization
|> #that has one table, i.e.
|> #
|> #Table Strings:
|> # String char(4) not null,
|> # Can1 char(4) not null,
|> # Can2 char(4) not null
|>
|> This is correct. Remember that you said denormalized. This is crucial -
|> saying that the model was first normalized and then decisions were made to
|> denormalize it. This is totally different from un-normalized.
|>
|> The question that I have here is whether there will EVER be more than two
|> cans? Note that if this is ever the case, the above model will simply not
|> work :-(

I am assuming in my question that we know that M in the M:1 relationship is always 2. There are always exactly 2 cans for a given string.

|>
|> #On the other hand, a pure "normalized approach" would say have two tables:
|> #
|> #Table Strings:
|> # String char(4) not null
|> #
|> #Table Cans
|> # String char(4) foreign key references Strings (String),
|> # Can char(4)
|> # Can_num char(1) /* Indicates whether can1 or can2 */
|> #
|> #or even three tables, Strings, Can1s and Can2s but lets ignore this option.
|> #
|> #Option 2 may seem frivolous and, in some ways it is,
|>
|> Reread the question posed above.
|>
|>
|> #I am also curious as to whether this type of modeling/design discussion has
|> #anyplace in this forum.
|>
|> You would think, Derek, that this type of discussion would be of extreme
|> importance to any software engineer, DB administrator, analyst, etc. that
|> wants to model data correctly. Lets sit back and see!

To state the question in a more abstract fashion: If, in a M:1 relationship, we know that M is a constant value, what are the advantages/disadvantages of normalization. It is my hope that by asking modeling questions, I can stimulate people to ask their own modeling questions and participate in a modeling dialogue.

-- 
Derek Fields
(derek_at_gamekeeper.bellcore.com)