Re: Design question

C. Derek Fields (derek_at_candyland.gamekeeper.bellcore.com) wrote:
: 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.
(Additional discussion deleted)

I have run across a lot of cases like this. I think it is a pretty common one (though I have worked a lot on applications very close to yours).

I go with the normalized version, usually. (There are always trade-offs, of course). This is only partly religious :-).

I was driven to this by winding up with lots of queries like:

	Select String
	From Strings
	Where Can1=:can
	Or Can2=:can;

instead of:

	Select String
	From Cans
	Where Can=:can;

This gets lots worse when you start joining these things.

I hate writing nasty queries. They are slow, hard to write, and I get them wrong.

: --
: Derek Fields
: (derek_at_gamekeeper.bellcore.com)

Gary Page (gbp_at_ssds.com)
1755 Park St.
Naperville, IL 60563 USA

(SSDS doesn't know I wrote this, so it must be just my ideas, right?) Received on Thu Jan 13 1994 - 00:25:13 CET