Link to home
Start Free TrialLog in
Avatar of dteshome
dteshome

asked on

Database Design Question

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)

Cardinality:
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?

Thank you.
D.
Avatar of 8080_Diver
8080_Diver
Flag of United States of America image

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?


Well, perhaps you need an additional column (Primary) in each of the Member -> Member_Type and Member_Firm -> Firm tables to indicate whether or not the relationship is classified as "Primary" for the Member involved.  This will require a constrraint being placed on that column such that only one row can be tagged as "Primary".
Avatar of dteshome
dteshome

ASKER

Thank you for your response, 8080_Driver. However, I don't understand the sol'n;
you say add ... additional col "Primary" in each table - Member, Member_Type??

If we consider only relationship between  Firm and Member, I already have a column that shows the relationship as "primary" or not - it is "isPrimaryFirm" with value 0/1 ... it identifies a Firm as being the primary Firm for the Member or not:

Firm_Member(idFirm, idMember, isPrimaryFirm)

What type of constraint do I place on this single column - isPrimaryFirm?
Sample data will be
idFirm    idMember   isPrimaryFirm
-------    ------------    ------------------
1            1                0
1            2                1
1            3                0
1            4                1

PKey for above table being the composite key (idFirm, idMember), how do I prevent the record
(1            4                1) from being created.

Thank you for your time.
D
)

I thought about this more ... I am going to alter the schema for tblMember and add a field, idPrimaryFirm with FKey constraint to tblFirm, and remove the isPrimaryFirm flag from Firm_Member altogether. This way, a Member belonging to several Firms with no primary will have multiple entries in Firm_Member and "null" in the isPrimaryFirm field ...

Is this what you were suggesting?

Thanks,
D
After giving it some thought, I think I'd fall back on the use of a Stored Procedure to handle the task of inserting or updating the data and, in that stored proc, I would make sure that, if an attempt is being made to assign the Member a Primary Firm or a Primary Member_Type, there hasn't already been one assigned.

Generally, though, if one allows multiple rows from Table_X to be associated with a single row from Table_Y, all of the Table_X rows are pretty much considered to be equal.  However, given that you wish to designate one of the Table_X rows as "Primary", the choices, as far as I can see are to do one of the following:

Have one column in the Members table for the Primary_Member_Type and another for the Primary_Firm and constrain those to have an entry in the corresponding cross-reference tables;
Create a stored procedure to handle the data manipulations and embed the business logic in the stored procedure;
Handle the enforcement of the rules in your application, possibly in a unit/assembly specifically dsigned for the Business Logic.
ASKER CERTIFIED SOLUTION
Avatar of 8080_Diver
8080_Diver
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you for your thought ... I appreciate your input.  I will take the approach of creating the corresponding PrimaryFirm and PrimaryMembType fields in the Member table and use FKey constraint.  Use SPs to handle inserts/updates, etc.

Thank you,
Dan