Re: Design question

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.

#1) These discussions are taking place in another group. If so, just point me

These discussions happen sporadically (sp?) from time to time in other groups.

#2) Nobody cares about designing databases right....

Many CARE about designing correctly. It must be noted, however, that correct design of a data model requires real work/study.

#3) Everybody is just waiting for somebody else to ask the first stupid question
# to get the ball rolling. So I will do the honors.
#
#
#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 :-(

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

#even though the Strings and
#the Cans tables really have many attributes each. The amount of data stored
#is almost identical in either option, except for the extra character to
#determine can type in the second version. However, the advantage of option two,
#the normalized option, is that I can write the insertion code (or object, since
#I am doing OO programming) once for each can and I create the string
#without having to know which cans it will connect to. Another good reason for
#the normalized approach is that, since Can1 and Can2 have the same set of
#attributes, should I change the format of one of the attributes, I only have
#to make one change, to the attribute in the Cans table. Otherwise, I have
#to make two changes, one for the Can1 attribute and one for the Can2 attribute
#in the Strings table.
#
#I am curious as to whether there are a) practical or b) religious reasons for
#doing one or the other.

It seems to me that you have given some excellent practical reasons for using a normalized data model. This is exactly the type of analysis that one should do when modeling data. There are many anomolies (read: problems) associated with an un-normalized data model in the areas of insertion, modification, and deletion. A normalized data model "corrects" these problems, depending on the level of normalization.

#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!

#--
#Derek Fields
#(derek_at_gamekeeper.bellcore.com)
Received on Thu Jan 13 1994 - 18:53:15 CET