One or the other rel'nships (not sure of tech. term of this scenario) 1

[Kraeg]

I'm seeking input/advice on a design issue.

I have a DB I'm designing that has an Individual (IND) entity, Organisation (ORG) entity, and a Membership (MBS) entity.
Each IND can have none or one MBS, the same with ORG. Each MBS has one of either IND or ORG (ie; one or the other, but it must have something).

So far, I've seen two ways to go about this:
1.
Having an INDid and ORGid field in MBS. Each MBS entity instance will have either the Primary Key of IND in the INDid field and a NULL in the ORGid field, or the Primary Key of ORG in the ORGid field and a NULL in the INDid field (so two one-to-one relationships where on its own each reference is optional from MBS to IND/ORG, but it is mandatory to have at least one of those references).
There's an additional field to specify the type of Membership: IND or ORG, to allow the system to know what attribute in MBS to refer to.
2.
Having an INDORG field that holds the PK of either the IND or the ORG entity, with the additional Membership type field indicating which entity (IND or ORG) to reference.

I began the design with option 1, but now I'm inclined to go with option 2, as it doesn't require a relationship, and that part of the system won't require referential integrity such as delete/update cascades.

Any thoughts or ideas? Other ways to model it? I hope I've explained it clearly.

 

[r937]

in both cases, you don't need the extra field, do you

do a search for subtype/supertype

i believe the foreign keys should be in the IND and ORG tables, referring back to the MBS primary key

r937.com | rudy.ca

 

[Kraeg]

There won't always be an MBS record for the IND and ORG records, so supertype/subtype isn't going to really do it.

I can now see why I wouldn't need the extra field for option 1; because I'd check whether IND_id or ORG_id is null to determine the Membership type (assuming the other field didn't somehow end up as null as well).
I'd still need it for option 2 though (INDORG), because there's no way to determine Membership type by looking at just the value in INDORG.

Basically, MBS has to have a one-to-one relationship with IND or with ORG, whereas IND and ORG don't have to have a relationship with MBS.

 

[r937]

yes but for option 2, you would LEFT OUTER JOIN to both IND and ORG, and get only one back anyway, so i still don't think you need the extra field

r937.com | rudy.ca

 

[ChrisHunt]

r937, assuming INDid and ORGid are just sequence numbers, how would you know whether a membership with INGORG=1 referred to the individual with INDid=1, or the org with ORGid=1 ?

Kraeg, There is a possible option (3) - If Individuals and Organisations have a lot of attributes in common (name, address, phone, etc.), replace them with a single entity which encompasses both. E.g. a "Member" entity, with an additional "member_type" attribute to indicate whether it's an individual or an organisation. If you like, you can create views which simulate seperate ind & org tables.

Of course this may not be applicable in your situation - it's hard to tell without knowing more about it.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[r937]

r937, assuming INDid and ORGid are just sequence numbers, how would you know whether a membership with INGORG=1 referred to the individual with INDid=1, or the org with ORGid=1 ?

by not allowing a duplicate across those two keys

they are subtypes of the supertype member


r937.com | rudy.ca

 

[Kraeg]

Requirements have changed a bit since I posted this, and I'm meeting with the users to discuss how to handle individual and organisation memberships (I'll also be a user of the system, but they've all used the current flat file system longer than I have so know more).

But basically, all individuals and organisations dealt with will be in the system. This includes members, banks, suppliers, contractors, etc. Memberships can be ind or org, but not all inds and orgs are members.
Some inds will also belong to orgs. For example:
A family membership would be in the org table, with a related record in the membership table. But each family member could have a rec in the ind table, relating to their family org record (but the ind family members wouldn't have a related membership record, as their membership comes under their family record).