[SQL] Database normalization
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
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 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 <[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
>
