12 replies to this topic

[jimlongo]

I keep running into some roadblocks, so I'm trying to take a step back and make sure my structure is making sense.
I have a community tennis club that I want to update our database form the flat structure I inherited.

We have households (Families) which may have any number of Members. Each Family will have a number of Transactions throughout the year. Most transactions are in regard to a single member, but we also have what is called a Family Membership which covers all the members of the family.

So a Family can have many Members and Transactions, a Member can have many Transactions and in the case of the Family Membership a transaction can apply to more than one member.
So wouldn't my ER diagram originally look like this?


IF that's correct, then I understand from sitting through some tutorials that the many to many relationship is hard to deal with so I've decomposed it to the following


Assuming that's correct I've then built the tables and relationships in FM like so


Does that make sense, and if not would you have a better suggestion?

Thanks for any help,
jim

[comment]

This is not entirely clear.

we also have what is called a Family Membership which covers all the members of the family.

What other option is there for a family? If all members of a family have their own club membership, why would they be a "family" to you?

[jimlongo]

Thanks for looking at this . . .

We have single Adult Memberships, single Junior Memberships, and Family Memberships.

A Household can have a Family Membership which would cover them all.
But they could have 1 Adult and 1 Junior Membership in a Household, or they might have 3 Junior Memberships, there are many combinations possible.

Maybe it would be clearer if we referred to a Household instead of a Family?

[comment]

The way I would read this (and please correct me if this is wrong, since it belongs to business rules, not data modeling) is that you have various memberships. A membership can have one or more people attached to it.

IOW, if there are 2 adult and 3 junior memberships that happen to belong to the same family, I don't see why this connection would concern you - they are just 5 different memberships of one person each.

[jimlongo]

I don't really see a Business rules forum, although there is a business Logic section.

Mostly I have the Families (Households) table for a single address for all those members and the fact that I'd want to send Tax Receipts, Shoe Tags, etc, to the household as opposed to having to send these items to each member.

[comment]

There is no business rules section, because the business rules are set by your business - and there's not much to discuss. They just need to be clear before you begin designing your database solution.


If the example given above is still a "family", then I would suggest something like:

Families -< Memberships -< Members

This way you'd have the same structure fitting the family that has purchased 5 individual memberships, as well as the single adult that has purchased a membership for himself only.


I am a little shaky on the subject of transactions, because I don't know what exactly a "transaction" is. Perhaps an example or two would help.

[jimlongo]

There is no business rules section, because the business rules are set by your business - and there's not much to discuss. They just need to be clear before you begin designing your database solution.

i see . . . These business rules as convoluted as they are have been in place a long time and have always been hard to understand.

Family memberships at a fixed price include up to 2 adults and as many juniors as live in that household. The Family membership is discounted so that it is more economical than 2 adults and 2 children. There are also single adult memberships, single junior memberships, and there are initiation fees for new families and new adults.

If the example given above is still a "family", then I would suggest something like:

Families -< Memberships -< Members

This way you'd have the same structure fitting the family that has purchased 5 individual memberships, as well as the single adult that has purchased a membership for himself only.


I am a little shaky on the subject of transactions, because I don't know what exactly a "transaction" is. Perhaps an example or two would help.

Transactions are any purchase. It could be a membership, junior lessons, adult lessons, summer camps. there are many different programs - about 60 different products. except for the family membership they all pertain to only one member.

i don't know if this is a complication, but in the case of any junior transaction, the government has a tax credit program that requires us tom send a tax receipt, so I have a parent of those juniors who will receive the tax credit receipt ( I have been calling them a member - type "TX"). the production of that receipt has been the stumbling block in a few other threads I've started recently. I keep getting about 80% of the way there until that report. This report needs to have all eligible payments for all the households junior transactions.

[comment]

about 60 different products. except for the family membership they all pertain to only one member.

Well, that's the snag here, isn't it? Because if you look at it that way, then you have an exception to the rule - and a pretty significant one, if family memberships cannot be in the same table as all the other memberships.

My suggestion was to look at the same business rules from a different perspective: I see a single adult as a family of one. Thus ANY membership is purchased by a family, the only difference is how many people are included in the purchased membership.


Another option is to put families and people in the same table, with a self-join relationship between a family and its members. This way any transaction, including a membership, can be assigned to a "contact" - either a family or an individual.

[jimlongo]

Well, that's the snag here, isn't it? Because if you look at it that way, then you have an exception to the rule - and a pretty significant one, if family memberships cannot be in the same table as all the other memberships.

My suggestion was to look at the same business rules from a different perspective: I see a single adult as a family of one. Thus ANY membership is purchased by a family, the only difference is how many people are included in the purchased membership.

So we seem to be back at the same place, I'm going to use Households instead of Families. A Household can have many Members and many Transactions.

Members >- Households -< Transactions

Do you think that's a reasonable approach? That's what I've been using for the most part up till now. Since every Transaction is "purchased" for a particular member I've simply been assigning the 1st member as the "purchaser" of the Family Membership. . . . it works for everything except that one tax report which I'll still have to figure out.

Another option is to put families and people in the same table, with a self-join relationship between a family and its members. This way any transaction, including a membership, can be assigned to a "contact" - either a family or an individual.

Wouldn't this require a self-join between the familyName and every memberName, so for practical purposes about 7 self-join relationships?
. . . that seems kind of unwieldy

[comment]

Members >- Households -< Transactions

Do you think that's a reasonable approach?

I am not sure. How will you handle renewals?

Another option is to put families and people in the same table, with a self-join relationship between a family and its members. This way any transaction, including a membership, can be assigned to a "contact" - either a family or an individual.

Wouldn't this require a self-join between the familyName and every memberName, so for practical purposes about 7 self-join relationships?

Why 7 self-join relationships? I see only one:

Contacts::ContactID = Contacts 2::FamilyID

A member in a family membership has a value in the FamilyID field, which relates him to the parent Family record.

[jimlongo]

Do you think that's a reasonable approach?

I am not sure. How will you handle renewals?

I just treat them as a different kind of membership. There are returning memberships (renewals) and new membership which have an added initiation fee.

Why 7 self-join relationships? I see only one:

Contacts::ContactID = Contacts 2::FamilyID

A member in a family membership has a value in the FamilyID field, which relates him to the parent Family record.

Do you mean simply
Contacts -<Transactions
with every member and family (household) in the Contacts table?

Thanks,
jim

[comment]

Do you mean simply
Contacts -<Transactions
with every member and family (household) in the Contacts table?

Yes.

[jimlongo]

Do you mean simplyContacts -<Transactions
with every member and family (household) in the Contacts table?

Yes.

Okay, that sounds even simpler . . . I'll work on that for a while.

Thanks for all your help!