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