Adding Family Members

[doughemi]

My client's members can own boats in a number of classes. I have a Members table, a join table Class Member Data, and a Classes table. This has worked great for years.

Now the client has decided that family members of a Member should be identified as separate owners of class boats. Until now, family members have been identified in a separate Family table (related to the Members table by Members::membershipNumber = Family::membershipNumber).

I am not sure how to modify my solution to accommodate this. Should family members be moved to the Members table with a flag field that indicates that they should not be considered as full members for other purposes (voting, magazine mailing list, etc)? Or is there a way to link the Family table to Class Member Data in such a way as to identify both Members and Family as Class Members?

[comment]

I have a Members table, a join table Class Member Data, and a Classes table.

This would indicate (to me) a many-to-many relationship between Members and Classes - where do the boats figure in this?

[doughemi]

Boats are a specific instance of a class. For example, Harvey Bilgepump may own Nirvana class # 125 and also Nirvana # 288 (and also Seawind #1255...etc).

Each specific registration number (RegNum) of ClassID registered to Member Y (membershipNumber) is a record in the Class Member Data table.

[doughemi]

In case some might have refrained from answering because of my use of the word "client", I'd like to state that this is NOT a commercial job; I developed this database as a volunteer for a non-profit organization. I'm not a professional developer (or even a semi-pro) and I could really use some help.

[eos]

My 5 cts: Since family members are also people, I'd put them into the Member's table, too, and use a flag to differentiate between “full” members and family, as per your original idea. Not only is this a natural fit, it has also has the advantage that (with some slight modifications) you can use the existing relationships and processes between the Members and the Class Member Data tables.

If you need to have a list of families/households, you could set up the flag as an ID field where family members have the ID of the original member, who's acting as "Head of the Household"; this would not only tell you that someone is a family member, but also who is the full member they belong to, on the one hand, and that someone has family members in the club, on the other.

[comment]

If I understand your description correctly, there is a separation between Memberships and Members: each membership can own several parts of several boats(?) and each membership can have multiple members assigned to it. One of the members is selected as the primary one - this is an attribute of the membership, not of the member.

[doughemi]

Not quite. Until now, there was a primary Member, who owned one or more Boats. The Member may or may not have a Family (consisting of one or more persons), but the Family of a Member had no other specific characteristics.

The issue I'm wrestling with now is that an individual designated as Family could now be identified as the owner of one or more boats, but will have none of the other characteristics of a Member. (Absolutely no logic there, I know, but that's what they want.)

A Boat cannot have more than one owner. Boats now identified as belonging to a Member will have to be transferred to the Family person as required, but this is trivial once we have the correct structure in place.

I thought of moving the Family to Members, as eos suggested, with a special ID (If the Member had MembershipNumber 12345, his Family would be 12345.01, 12345.02, etc) allowing the use of If[MembershipNumber = Int(MembershipNumber); blah; no_blah] calculations/scripts. But I was hoping that there might be a simpler, less obvious, way.

[comment]

A Boat cannot have more than one owner.

Then why the join table?

I am not sure what you mean by "characteristics of a Member". I believe this is more an issue of business rules than of structure (or perhaps better put: the business rules issue needs to be clear before the structural issue can be addressed).

[doughemi]

Then why the join table?

I felt it necessary between Members and Classes.

Members				 Class Member Data		   Classes

pMemberID------------------------fMemberID

				   fClassID----------------------pClassID

				   BoatRegNum

				   RegDate

				   etc

Other reports and operations are required for classes, so I thought they should be in a separate table.

I am not sure what you mean by "characteristics of a Member". I believe this is more an issue of business rules than of structure (or perhaps better put: the business rules issue needs to be clear before the structural issue can be addressed).

You're right, a business rules issue. Members can

• Vote
  
• Receive Quarterly Magazine
  
• Receive renewal reminders
  
• Receive bulletins
  

Family members do none of these things; that's why they are in a separate table.

[Vaughan]

The join table appears to be boats. That would make more sense.

The ability for a membership to vote is another attribute. I think there should be a "Memberships" table, which Members then link to. The number of votes is the number of (active) Membership records. You could then create a solution to manage the memberships i.e., sending out renewal notices, tracking payments etc.

I agree with Comment regarding the confusion around business rules: sometimes business rules are themselves confused or even self-contradictory. Work these out first. If it's a club then check the constitution and see what it has to say about memberships. I've been in a few associations where "family" and other types of memberships aren't actually allowed for.

[comment]

I am still confused. I think I know what members and boats are; I have no idea what "classes" means in this context.

In any case, I agree with Vaughan agreeing with me : you can have all the people in a Members (or Contacts) table, but the entity that is entitled to vote and receive stuff deserves a table of its own.

[doughemi]

Thanks, guys. I now see that I was pursuing this from the wrong end. I will go for a Contact table.

PS A "class" in this context is a set of boats which conform to a stringent set of class rules, such as length, draft, weight, sail area, and so on. All these parameters affect speed. Racing within a class allows much more competetive racing without needing to calculate and administer handicaps.

[Vaughan]

I'd be a bit careful with this Classes thing because it's possible for keel and centreboard boats to race under multiple classes at different times. For instance with centreboards, a Laser could sail as one of 3 classes depending on the rig. The 49er just got a smaller rig so there are 2 classes of them now; the 29er has 2 rigs too...

I'd be tempted to have the "class" as part of a boat's entry for each race/regatta. The Boat record would have the sail or hull number (or both) and maybe hull type and maker, so there might be a record for Laser 190267 but it would not be identified any further. However, look at the "Races" portal and see that it's been sailed as a 4.7 and Radial at different times and in different races by different people: by the kids in the morning with a 4.7 rig and a parent in the afternoon with a Radial rig.

I know Laser sailors that rock up to a regatta with both radial and full rigs and decide which class to enter under based on the weather report! (The radial rig is smaller so it's easier to mange in strong wind, while the full rig would be faster and more fun in lighter winds.)