Re: Database normalization - Mailing list pgsql-sql
From | Sid 'Neko Tamashii' |
---|---|
Subject | Re: Database normalization |
Date | |
Msg-id | f79d5c740805060533g53affeb1y81098a891f2aee1c@mail.gmail.com Whole thread Raw |
In response to | Re: Database normalization (Richard Huxton <dev@archonet.com>) |
List | pgsql-sql |
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 />