I don't like this schema.
1. It makes more sense to have an 'contact_id' as a column on Company &
Account , than vice versa. A contact is a representative for those
entities, not the other way around. I think you're running into issues,
because you seem to be trying to adapt this real-world relationship into
your schema ( ie, what to do when a contact leaves an account company, and
all the work needed to backtrack it ; flipping the columns should make that
simpler )
2. It might make more sense to have another table that just handles
relationships :
Contact2Relation
contact_id NOT NULL
account_id NULL
company_id NULL
role_id
timestamp_of_relation
-- add constraint on account_id OR company_id
in that table, you NEVER have both account & company. it's either-or.
If a contact is related to both an account and a company, they get 2
entries.
if you want to deal with historical data, you can even drop 2 more
columns on it:
is_active BOOL
timestamp_deactivate TIMESTAMP
then you can just filter records to see historical contacts on the
account/company
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
