Re: Database normalization

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