Modeling an organization with annual membership dues

[Tom R.]

Hi,

 

I'm trying to work up a relatively simple database to help run an organization I belong to. I've figured out that I could have tables for People, Organizations, and Memberships ( a joint between People and Organizations ). What is tripping me up is how to best account for the fact that members have to pay annual dues to renew their memberships. Also, this is a professional organization, and some members pay their own dues, while some members' dues are paid by their employer. I figure my organization would be one record in the Organizations table, and employers could be additional records in the same  table. I could also add a Payments table to track checks. But I'm drawing a blank on how to connect everything. Not sure whether to make the membership period an attribute of Memberships, or whether I should have a separate table for each year of membership.

 

Any help would be appreciated.

 

Thanks,

 

Tom

[Matthew F]

 

 

Not sure whether to make the membership period an attribute of Memberships, or whether I should have a separate table for each year of membership.

I would think that you would want the membership period to be an "attribute" of memberships (i.e. a dedicated field on the membership record).  Alternatively, you might want to create a new record for each year of membership, but not a separate table.  Different years would be better designated by a date field. 

[comment]

Not sure whether to make the membership period an attribute of Memberships, or whether I should have a separate table for each year of membership.

 

That's a good question. Will there be attributes that are common to all periods of a specific membership - other than MemberID and OrganizationID?

[Tom R.]

I can't think of anything other than MemberID and OrganizationID that would be common to all periods of a membership. Matthew, I like your suggestion of just creating a new Membership record for every year. I came up with the ERD below. I've got a table for Payments, and in the diagram there's also a table for Payors. I could just have a foreign key in Payments that would accept either a MemberID or an OrganizationID as the payor, or I suppose I could create an actual Payor table that would serve as kind of a wrapper for both Members and Organizations. Any thoughts on this aspect?

 

Thanks!

 

Tom

 

PS - well, I don't see how to attach an image, so no ERD. 

[comment]

I can't think of anything other than MemberID and OrganizationID that would be common to all periods of a membership.

 

Well, then a "wrapper" (i.e. parent) table for membership periods is not required. Except... what if there is a payment that covers more than one membership period (fully or partially)? In such case you need to either [a] split the one payment manually into several, or apply all payments against the parent membership record and calculate the balance there. In the latter scenario the membership record would have some attributes, namely the balance.

[Tom R.]

I guess that is theoretically possible. However, for our organization dues are always paid at the beginning of the year and are not pro-rated; so I don't think that would be likely. But never say never, I suppose. 

 

Thanks!

[comment]

for our organization dues are always paid at the beginning of the year and are not pro-rated

 

Okay, so in the worst case you can always use the [a] option. But there is something else puzzling me: what are the other organizations in your Organizations table? if yours is the only organization you are tracking (with regards to memberships), you could simplify things considerably. For one thing, the join table is not required.

[Tom R.]

My thought was, in addition to my organization, to use the Organization table to store records for employers, and then employees would be members of the employer Organization. 

[comment]

If you like, you could put everyone -  members and employers alike - into a single Contacts table (provided the fields required to describe them are more or less the same). However, that does not change the nature of the relationships between them - and going by your description, there is no many-to-many relationship between organizations and members. In fact, it looks like Payments and MembershipPeriods are one and the same thing, so ostensibly you could do it all with just two tables and two or three relationships, e.g.:

 

Contacts (as Employers) -< Contacts (as Members) -< MembershipPeriods >- Contacts (as Payors)

[Matthew F]

I agree with Comment's suggestions.  MembershipPeriods is equivalent to Payments since clients pay exactly once per year.  MembershipPeriods needs to have 2 foreign keys: MemberID and PayorID.  Both of these link to different Contacts table occurrences.  In the case of Payors, the contact can be a person or an organization.  You might want a little check box on Contacts to distinguish organizations from people. (Although the Supreme Court might say that there's no difference between the two). 

[Tom R.]

Thanks Matthew and Comment for your suggestions. One thing I forgot to mention is that some Employers write a single check to pay for the memberships of multiple employee/members. Do you think the schema you described would handle that scenario?

 

Best regards,

 

Tom

[comment]

One thing I forgot to mention is that some Employers write a single check to pay for the memberships of multiple employee/members.

 

Aha.

 

what if there is a payment that covers more than one membership period (fully or partially)?

 

Obviously, you will need to add a Payments table to handle this. Still, there is the question at what level of detail do you want to handle it. For example, you could have a Payments table linked to the MembershipsPeriods through a join table. Or you could make it simpler and link the Payments to the Contacts table. Note that the sum of membership period dues a contact is related to as a payor, minus the sum of payments the same contact is related to, equals the balance the contact owes.

[Matthew F]

comment:  Obviously, you will need to add a Payments table to handle this... For example, you could have a Payments table linked to the MembershipsPeriods through a join table...

I agree that you'll want a payments table since there can be more than one MembershipPeriod per payment.  However, I don't think you would need an intervening join table since this would be a 1-to-many relationship (Payor::MembershipPeriod) not a Many-to-Many relationship.  At least I would think that member never has more than one payor for a given year.