Обсуждение: Database 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}

  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 }

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

client:
  id: {type: integer}

users:
  user_id: {type: integer, primaryKey:true, foreignTable: client, foreignReference: id}
  id: {type: integer}

profiles:
  client_id: {type: integer, primaryKey:true, foreignTable: client, foreignReference: id}
  id: {type: integer}

userprofile:
  client_id: {type: integer, primaryKey:true}
  user_id: {type: integer, primaryKey:true}
  profile_id: {type: integer, primaryKey:true}
  _foreignKeys:
    fk_user:
      foreignTable: users
      references:
        - { local: client_id, foreign: client_id }
        - { local: user_id, foreign: id }
    fk_profile:
      foreignTable: profile
      references:
        - { local: client_id, foreign: client_id }
        - { local: profile_id, foreign: id }