• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

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.
0
dteshome
Asked:
dteshome
  • 3
  • 3
1 Solution
 
8080_DiverCommented:
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".
0
 
dteshomeAuthor Commented:
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
)

0
 
dteshomeAuthor Commented:
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
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
8080_DiverCommented:
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.
0
 
8080_DiverCommented:
I would be careful in how I word the FK Constraint.  You may even want to have an entry in each of the Member_Types and Firms (e.g. ID = 0) with the decription "No Primary Designate" and have that ID designated as the default value for the columns in the Members table.
0
 
dteshomeAuthor Commented:
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
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now