Design question

From: C. Derek Fields <derek_at_candyland.gamekeeper.bellcore.com>
Date: 12 Jan 1994 21:27:10 GMT
Message-ID: <2h1pve$a23_at_athos.cc.bellcore.com>


<<Introductory Note>>>

I am posting this here because 1) comp.databases is hopelessly restricted to msdos databases (or front-ends) and 2) because I am an Oracle dba and regularly read this newsgroup, so I thought this would be a good place to start
<<End Introductory Note>>

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:

  1. These discussions are taking place in another group. If so, just point me in the right direction.
  2. Nobody cares about designing databases right. If so, let me know now and I won't belabor the point.
  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

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

I am also curious as to whether this type of modeling/design discussion has anyplace in this forum.

-- 
Derek Fields
(derek_at_gamekeeper.bellcore.com)
Received on Wed Jan 12 1994 - 22:27:10 CET

Original text of this message