On Nov 26, 2013, at 11:23 PM, John Kida <jdk...@gmail.com> wrote: > Sorry I slightly misrepresented the schema. An Account does not have a > company id column. So in your opinion what's the best option here. I have a > company that may or maynot have a reference to an account.
OK well assuming the Company can have at most *one* account, you’d have Company.account_id > However both the company and the aacount tables need to have a way to link > multiple contacts to them. so here it depends on how a particular contact is allowed to be linked to a parent. Can a single contact row belong simultaneously to more than one company and/or account at a time? or are contacts local to a single parent company and/or account? If the former, you’d have two more tables, company_contact, and account_contact, which link company and account to the “contacts” table using a many-to-many pattern. if the latter, then you’d want contacts that are “owned” by the parent company or parent account - you’d break it into two tables - company_contact and account_contact. the advantage to this pattern is that a particular contact can be modified locally to its parent, without impacting that same contact as referenced by some other row somewhere. There’s the third option you mentioned which is that the “contact” table has a foreign key to company and account, and one must be null. That approach isn’t terrible but anytime you allow an FK column to be nullable, the schema becomes more complicated. I’d use separate company_contact, account_contact tables. With SQLAlchemy declarative you can use a mixin/abstract base so that you still only have to define the table definition for both just once.
signature.asc
Description: Message signed with OpenPGP using GPGMail
