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.

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

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 <dev@archonet.com> 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
>