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