Database Design Question
Posted on 2011-02-11
I have several relations of the following type:
Table: Member(idMember, ...) , MemberType(idMemberType, ...), Member_MemberType((idMember, idMemberType, isPrimaryMemberType, ...) - junction table
Table: Firm(idFirm, ...), Member(idMember,...), Firm_Member(idFirm, idMember, isPrimaryFirm)
Member---> Member_MemberType is 1-many (a member has many types)
MemberType ---> Member_MemberType is 1-many
Firm --> Firm_Member is 1-many (a Firm has has many Members)
Member --> Firm_Member 1-many (a Member belongs to many Firms)
PKeys for the junction table "Firm_Member" is the composit key (idFirm,IdMember) ...
My question is, how do I ensure that a Member has only one MemberType flagged as its "primaryMemberType"?
In the same way, how do I ensure that a Member has only one Firm flagged as its PrimaryFirm?