Skip site navigation (1) Skip section navigation (2)

Re: Database normalization

From: Richard Huxton <dev(at)archonet(dot)com>
To: Sid 'Neko Tamashii' <gatoelho(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Database normalization
Date: 2008-05-06 12:28:14
Message-ID: 48204EDE.7080002@archonet.com (view raw or flat)
Thread:
Lists: pgsql-sql
Sid 'Neko Tamashii' wrote:
> Is this model (Symfony's YML based) wrong based on normalization?
> 
> propel:
>>   client:
>>     client_id: {type: integer}
>>
>>   foo:
>>     client_id: {type: integer, foreignTable: client, foreignReference: client_id}
>>     foo_id: {type: integer}
>>
>>   bar:
>>     client_id: {type: integer, foreignTable: client, foreignReference: client_id}
>>     bar_id: {type: integer}

Well, assuming the primary-key on these includes both columns - e.g. 
(client_id,foo_id)

>>
>>   foobar:
>>     client_id: {type: integer}
>>     foo_id: {type: integer}
>>     bar_id: {type: integer}
>>     _foreignKeys:
>>       fk_foo:
>>         foreignTable: foo
>>         references:
>>           - { local: client_id, foreign: client_id }
>>           - { local: foo_id, foreign: foo_id }
>>       fk_bar:
>>         foreignTable: bar
>>         references:
>>           - { local: client_id, foreign: client_id }
>>           - { local: bar_id, foreign: bar_id }

This looks fine (assuming not-null on all columns).

You could make an argument for an explicit foreign-key for client_id 
too, but it's clearly safe not to have one while the other two 
foreign-keys are there. If you allow client_id to be set separately from 
foo_id/bar_id then you'll want the foreign-key of course.

The one thing I would do is change the names of foo_id, bar_id since 
they're not identifiers by themselves.

-- 
   Richard Huxton
   Archonet Ltd

In response to

Responses

pgsql-sql by date

Next:From: Sid 'Neko Tamashii'Date: 2008-05-06 12:33:48
Subject: Re: Database normalization
Previous:From: Sid 'Neko Tamashii'Date: 2008-05-06 11:06:39
Subject: Database normalization

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group