On Nov 25, 2013, at 6:35 PM, John Kida <jdk...@gmail.com> wrote: > Lets say i have the following table schema > Account > id > company_id (NOT NULL ForeignKey(Company.id)) > > Company > id > account_id (ForeignKey(Account.id)) ALLOW NULL > other cols... > > Contact > constraint = (account_id OR company_id) (both cant be NULL) > id > account_id (ForeignKey(Account.id) > company_id (ForeignKey(Company.id) > > So basically I have a Account table that must have a company_id, However I > have a Company table that doesnt have to have an account_id.. think of the > company as like a new lead for a sale, that might not be an account yet.
what does it mean for an Account to have a company_id, versus a Company that has an account_id? A mutually dependent foreign key situation like this usually implies that there’s some single Account that is “favored” among a collection of accounts, or vice versa. See http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#rows-that-point-to-themselves-mutually-dependent-rows for discussion of this. > > Then i have some contacts, so the contact must have either a relation to a > Company or an Account. So im not sure if you can see the problem but it lies > in the fact that a Contact could have an account_id that references 1 > account, and a company_id that is a Company that references a different > account_id. > > So my question is in what ways can i solve this problem. How can i ensure > that if a Contact has an account_id value that only a company_id value of a > Company with that account_id can be present. So far it looks like the schema has too many foreign keys involved. E.g. a more normalized version would have only Contact.account_id, and you’d get to Company via Contact.account_id -> Account.company_id. Otherwise, when you have two different ways to navigate to the same thing, that’s data duplication. Keeping them in sync is awkward. If you truly needed it that way, then you’d probably want to create a trigger of some kind that ensures this condition.
signature.asc
Description: Message signed with OpenPGP using GPGMail
