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