Skip site navigation (1) Skip section navigation (2)

Re: Database normalization

From: "Sid 'Neko Tamashii'" <gatoelho(at)gmail(dot)com>
To: "Richard Huxton" <dev(at)archonet(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Database normalization
Date: 2008-05-06 12:33:48
Message-ID: f79d5c740805060533g53affeb1y81098a891f2aee1c@mail.gmail.com (view raw or flat)
Thread:
Lists: 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(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
>

In response to

pgsql-sql by date

Next:From: Achilleas MantziosDate: 2008-05-07 11:42:25
Subject: dbmirror - migration to 8.3 from 7.4
Previous:From: Richard HuxtonDate: 2008-05-06 12:28:14
Subject: Re: Database normalization

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group