Re: [NTLK] Soups and the Newton's downfall

From: Zachery Bir (zbir_at_urbanape.com)
Date: Fri Sep 07 2001 - 09:14:16 EDT

On Thursday, September 6, 2001, at 04:43 PM, Jon Glass wrote:

>
> on 9/6/01 3:09 PM, Michael J. Hu=DFmann at michael_at_michael-hussmann.de
> wrote:
>> Databases can be quite sophisticated, so I don't understand what you
>> are
>> getting at. It might be a good idea to get more technical, I guess.
>
> Can a record in a database contain another record? Can you add fields
> to one
> record but not add them to another. Can you put a work phone in a phone
> field in one record, but a work field in the same place in another
> record,
> and have the database automatically keep them straight? I have some
> Newton
> names that have more phones than most desktop apps have allowed for
> years.

Yes, it's called normalization, and while it can get carried away, it is
quite effective when you don't know the full extent of data that will
map to a single "entity".

For example ( in pseudocode ):

   CREATE TABLE person ( id VARCHAR( 8 ), first_name VARCHAR( 50 ),
last_name VARCHAR( 50 ) );

   CREATE TABLE phone ( id VARCHAR( 8 ), person_id VARCHAR( 8 ), tag
VARCHAR( 10 ), area_code VARCHAR( 3 ), prefix VARCHAR( 3 ), exchange
VARCHAR( 4 ), ext VARCHAR( 4 ) );

For instance, now you populate the person with a unique individual:

   INSERT INTO person ( id, first_name, last_name ) VALUES ( '34fe25rw',
'Zachery', 'Bir' );

And with that unique id, you can associate as many phone numbers with
that individual as you have storage space:

   INSERT INTO phone ( id, person_id, tag, area_code, prefix, exchange,
ext ) VALUES ( '1w3f52wr', '34fe25rw', 'Home', '804', '353',
'3742', '' );

   INSERT INTO phone ( id, person_id, tag, area_code, prefix, exchange,
ext ) VALUES ( '54edhy4r', '34fe25rw', 'Work', '804', '644', '5109',
'111' );

   INSERT INTO phone ( id, person_id, tag, area_code, prefix, exchange,
ext ) VALUES ( '34rui90p', '34fe25rw', 'Fax', '804', '644', '5410', '' );

I use these conventions all the time. You can easily offload anything
that a unique entity can have multiples of onto other tables, and then
you eliminate the necessity to try and build your schema to account for
every possibility.

me = { 'name' : 'Zachery Bir', 'email' : 'zbir_at_urbanape.com',
        'voice' : '(804) 353-3742', 'url' : 'http://www.urbanape.com/' } 

--
This is the Newtontalk mailinglist - http://www.newtontalk.net
To unsubscribe or manage: visit the above link or
	mailto:newtontalk-request_at_newtontalk.net?Subject=unsubscribe

This archive was generated by hypermail 2.1.2 : Wed Oct 03 2001 - 12:01:29 EDT