Re: Normalization Question

From: Marshall Lucas <mlucas_at_liapartners.com>
Date: Thu, 27 Jan 2005 11:59:24 -0600
Message-ID: <0_9Kd.1471$7J.217@okepread04>

Dawn M. Wolthuis wrote:
> "Leandro Guimarães Faria Corsetti Dutra" <leandro_at_dutra.fastmail.fm> wrote
> in message news:pan.2005.01.21.06.55.35.625887_at_dutra.fastmail.fm...
>

>>Em Fri, 21 Jan 2005 00:19:31 +0100, BG escreveu:
>>
>>
>>>To abide by the rules of normalization, should the phone & date
>>>fields be placed into their own tables?
>>
>>No, but that may be good design anyway as you may want to save
>>more than one telephone of each type, for example.

>
>
> It is such a shame that you have to guess at future cardinality right up
> front when designing a relational structure, eh? Wouldn't it be great if we
> could design at a level where a change in multiplicity, like a change in
> size, type, or constraints, could be handled without a changing the logical
> model of the data? smiles. --dawn
>
>

>>-- 
>>Leandro Guimarães Faria Corcete Dutra <leandro_at_dutra.fastmail.fm>
>>Maringá, PR, BRASIL                            +55 (44) 3025 6253
>>http://br.geocities.com./lgcdutra/             +55 (44) 8803 1729
>>Soli Deo Gloria!                               +55 (11) 9406 7191
>>

>
>
>

Actually, you can. Check into Multivalued Databases. I would store my record like:

ID
FirstName]LastName
Address1]Address2]Address3
Zipcode
PhoneType1]PhoneType2]...
Phone1]Phone2]...
...

The ] is a value mark and delimits the data. You could do something similar in SQL, but not without wasting tons of space and sacrificing search capabilities. With multivalues I have a single "dictionary" entry that defines PHONE as field 5 and it will search all values within that field, or if I constrain it to a PHONETYPE of 'cell' it will only pickup those values that are the same positionally within PHONE as those in PHONETYPE that equal 'cell'.

The other nice thing is that the record is only as long as the data plus the delimiters. My dictionaries tell the system a display length, but I can store as much or as little as I want.

Example record:

1
Marshall]Lucas
123 W. Somewhere
73003
H]C]I
14112131234]2318923]011123123445

Where H=Home, C=Cell, I=International

I could type:
LIST filename BY-EXP PHONETYPE="C" PHONE

I would get

Record	PHONE
1	231-8923

properly formatted, if I told my dictionary entry how to do so. And it would handle all cases of formatting.

If I type:
LIST filename PHONE

I get

Record	PHONE
1	1-411-213-1234
	231-8923
	0-11-123-123-445

Not sure on that last formatting, didn't check against country code 11.

I guess I'll shut up and go back to my corner now.

Marshall Received on Thu Jan 27 2005 - 11:59:24 CST