Re: Database normalization

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(at)archonet(dot)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
>