Re: Normalization Question

From: Dawn M. Wolthuis <dwolt_at_tincat-group.comREMOVE>
Date: Thu, 27 Jan 2005 23:17:15 -0600
Message-ID: <ctcht3$od0$1@news.netins.net>

"Marshall Lucas" <mlucas_at_liapartners.com> wrote in message news:0_9Kd.1471$7J.217_at_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.

Very cool -- thanks for coming out of that corner. We don't know each other, but some might have guessed otherwise. I'm pleased to see someone else interested in database theory and aware that there is a viable model underlying the old pick databases -- there are a few MV folks at least listening here if not piping up on this forum.

If you haven't been there before, check out www.u2ug.org (I'm the chair of the emerging International U2 User Group for IBM's U2 products). It isn't that I think pick is the future, but it sure beats RDBMS implementations on so many fronts that I think we need to start with that data model (or an XML data model which is very similar) and move forward, rather than starting with the "old fashioned" 1NF relational model. Cheers! --dawn

> 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 - 23:17:15 CST