Thread: 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

To be more clear:<br /><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt
0pt0pt 0.8ex; padding-left: 1ex;">client:<br />  id: {type: integer}<br /><br />users:<br />  user_id: {type: integer,
primaryKey:true,foreignTable: client, foreignReference: id}<br />   id: {type: integer}<br /><br />profiles:<br /> 
client_id:{type: integer, primaryKey:true, foreignTable: client, foreignReference: id}<br />  id: {type: integer}<br
/><br/>userprofile:<br />  client_id: {type: integer, primaryKey:true}<br />   user_id: {type: integer,
primaryKey:true}<br/>  profile_id: {type: integer, primaryKey:true}<br />  _foreignKeys:<br />    fk_user:<br />     
foreignTable:users<br />      references:<br />        - { local: client_id, foreign: client_id }<br />         - {
local:user_id, foreign: id }<br />    fk_profile:<br />      foreignTable: profile<br />      references:<br />       
-{ local: client_id, foreign: client_id }<br />        - { local: profile_id, foreign: id }<br /></blockquote><br
/>Eachclient has it's own profiles and users, and each user has some profiles<br />The idea is to enforce the value of
client_idto be the same at all moments<br /><br /><div class="gmail_quote">On Tue, May 6, 2008 at 9:28 AM, Richard
Huxton<<a href="mailto:dev@archonet.com">dev@archonet.com</a>> wrote:<br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="Ih2E3d">Sid
'NekoTamashii' wrote:<br /><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204);
padding-left:1ex;"> Is this model (Symfony's YML based) wrong based on normalization?<br /><br /> propel:<br
/><blockquoteclass="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">  client:<br />
  client_id: {type: integer}<br /><br />  foo:<br />    client_id: {type: integer, foreignTable: client,
foreignReference:client_id}<br />    foo_id: {type: integer}<br /><br />  bar:<br />    client_id: {type: integer,
foreignTable:client, foreignReference: client_id}<br />    bar_id: {type: integer}<br /></blockquote></blockquote><br
/></div>Well, assuming the primary-key on these includes both columns - e.g. (client_id,foo_id)<div class="Ih2E3d"><br
/><br/><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); padding-left:
1ex;"><blockquoteclass="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><br />
 foobar:<br/>    client_id: {type: integer}<br />    foo_id: {type: integer}<br />    bar_id: {type: integer}<br />  
 _foreignKeys:<br/>      fk_foo:<br />        foreignTable: foo<br />        references:<br />          - { local:
client_id,foreign: client_id }<br />          - { local: foo_id, foreign: foo_id }<br />      fk_bar:<br />      
 foreignTable:bar<br />        references:<br />          - { local: client_id, foreign: client_id }<br />          - {
local:bar_id, foreign: bar_id }<br /></blockquote></blockquote><br /></div> This looks fine (assuming not-null on all
columns).<br/><br /> You could make an argument for an explicit foreign-key for client_id too, but it's clearly safe
notto have one while the other two foreign-keys are there. If you allow client_id to be set separately from
foo_id/bar_idthen you'll want the foreign-key of course.<br /><br /> The one thing I would do is change the names of
foo_id,bar_id since they're not identifiers by themselves.<br /><font color="#888888"><br /> -- <br />  Richard
Huxton<br/>  Archonet Ltd<br /></font></blockquote></div><br />