[SQL] Database normalization

[Sid 'Neko Tamashii']

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}
>
>   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 }
>
>
The full discussion can be found at
http://www.symfony-project.org/forum/index.php/t/12807/

All I want is the best option, but not based in common sense, but in rules,
cause after a lot of discussions I found this way better than the usual one.

Re: [SQL] Database normalization

[Richard Huxton]

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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Database normalization

[Sid 'Neko Tamashii']

To be more clear:

> 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 }
>

Each client has it's own profiles and users, and each user has some profiles
The idea is to enforce the value of client_id to be the same at all moments

On Tue, May 6, 2008 at 9:28 AM, Richard Huxton <[EMAIL PROTECTED]> wrote:

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