Jonathan, Michael Ignoring the debate on overloading a table to cater for multiple relationships, I think we are nearly back we we started i.e. a table where we would like to add an either or constraint.
Other than database triggers or (before_flush, before_commit), is the a declarative SQLAlchemy way to this? Cheers Rob On Wednesday, November 27, 2013 4:58:51 PM UTC, Jonathan Vanasco wrote: > > 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.
