Table Structure

[Stewart Allen]

Hi all,

I'm designing a club database and have encountered a problem when trying to
extract the total amount of fees that a Student/Family is suppose to pay
during their time of membership. I've already designed the tables and
relationships but I want your advice to see if my design was right.

In my older version a member pays the fees but when there's 2 or more
members that belong to a family (brother, sister, mother, father etc), it's
the family that pays the fees. My solution was when a person joins, that
person is assigned to a current family or a new family is created if this
member doesn't have any family enrolled. If there are more than one person
that belong to a family, they pay discounted rates (family rates) as long as
there are more than one family member still enrolled. I need to record when
each member of the family joined (they all could have joined at the same
time or at different times) and when the discounted rates are applied.

When one person of the family leaves the rates are adjusted accordingly and
the date when this happened should also be recorded. To complicate things
more that person who left may come back again at a later date (or maybe a
different person of the family) so the fees should be set again.

To sum up:
A member belongs to a family but a family can have many members enrolled.
It's the family the pays the fees even if the family consists of one member.
Need to keep track of when a member enrols, leaves and maybe re-enrols.
Need to track when extra members of a family joins and leaves and what the
fees that family should be paying during that time period.
Need to record when fees were paid.

If you think that a member should pay fees is a better idea than a family,
let me know if that is a better solution but when more than one person of
the family joins, each individual will pay a reduced rate for the period of
time that there are more that 1 family member enrolled at the same time.

My final goal is to work out how much that family is supposed to have paid
from the date the first member joined when the amount of fees that family
pays per payment period will change throughout time.

Any ideas on a table structure? If you think I'm on the wrong track, please
guide me on the right path.

TIA
Stewart

P.S. Will post my current table structure in a day or two or after there has
been a couple of replies

[rkc]

"Stewart Allen" <sa****@ThisPar tNotVailid.wave .co.nz> wrote in message
news:bu******** **@news.wave.co .nz...

I'm designing a club database and have encountered a problem when trying to extract the total amount of fees that a Student/Family is suppose to pay
during their time of membership. I've already designed the tables and
relationships but I want your advice to see if my design was right.
<snip stuff>
Any ideas on a table structure? If you think I'm on the wrong track, please guide me on the right path.

Don't know what track you're on because you didn't post what you say
you have done.

Here's my first impression.

Group (GroupID, RateID)
Member (MemberID)
Membership(Memb erID, GroupID, StartDate, EndDate)
Rate (RateID, Duration, TotalMembers, Amount)

I'll explain why if you care.

[Stewart Allen]

"rkc" <rk*@yabba.dabb a.do.rochester. rr.nope> wrote in message
news:lX******** *********@twist er.nyroc.rr.com ...

<Snip>
Don't know what track you're on because you didn't post what you say
you have done.

Here's my first impression.

Group (GroupID, RateID)
Member (MemberID)
Membership(Memb erID, GroupID, StartDate, EndDate)
Rate (RateID, Duration, TotalMembers, Amount)

I'll explain why if you care.

My interpretation of your answer.
*Group table*
GroupID (PK)
RateID (FK) from Rate table

*Member table*
MemberID (PK)

*Membership table*
MemberID (FK) from Member table
GoupID (FK) from Group table
StartDate
EndDate

*Rate table*
RateID (PK)

Membership is an intermediate table between Member and Group so there's a
many to many relationship between the two. The rate is assigned to the group
which includes the duration of the amount the group pays and the total
amount of members at that time.

I have a couple of question regarding this.
How do you store the duration when they're still enrolled? e.g.
MemID FName LName
1 Joe Smith
2 Sue Smith
3 Fred Smith
All belong tho the same family so special rates apply.

Joe joined first and the rate is $20.00 per period. Sue joins 2 months later
and now that rate is $30.00 for both of them. Fred joins 3 months later and
now the rate is $40.00 for all three of them. 1 month later Sue leaves so
the rate reduces back down to $30.00. Joe and Fred are still enrolled at the
club so how do you store the duration if the membership is still current? If
Joe started 1 year back and the payment periods are monthly, the total
amount that this family is supposed to have paid is $350.00. After 1 more
month of membership the total due is $380.00.

With my example above and the Membership table having 2 FKs to create the
PK, what happens when the case arises when Sue leaves or is the StartDate a
FK too?

Stewart

[rkc]

"Stewart Allen" <sa****@ThisPar tNotVailid.wave .co.nz> wrote in message
news:bu******** **@news.wave.co .nz...

Membership is an intermediate table between Member and Group so there's a
many to many relationship between the two. The rate is assigned to the group which includes the duration of the amount the group pays and the total
amount of members at that time.

I have a couple of question regarding this.
How do you store the duration when they're still enrolled? e.g.
MemID FName LName
1 Joe Smith
2 Sue Smith
3 Fred Smith
All belong tho the same family so special rates apply.

Joe joined first and the rate is $20.00 per period. Sue joins 2 months later and now that rate is $30.00 for both of them. Fred joins 3 months later and now the rate is $40.00 for all three of them. 1 month later Sue leaves so
the rate reduces back down to $30.00. Joe and Fred are still enrolled at the club so how do you store the duration if the membership is still current? If Joe started 1 year back and the payment periods are monthly, the total
amount that this family is supposed to have paid is $350.00. After 1 more
month of membership the total due is $380.00.

With my example above and the Membership table having 2 FKs to create the
PK, what happens when the case arises when Sue leaves or is the StartDate a FK too?

First, the Group table doesn't need the RateID. It's retrieved from the Rate
table
on an individual basis.

My thought was that the Membership table would track one membership period
for one member. The GroupId would be used to count the number of members
in that group at the time the fee is paid. Every member would be in a
group, even
if that member is the only person in the group. The fee would be based on
the number of people in the Group and the duration of the membership. The
amount due would be taken from the Rate table based on the number of
members and the length of time signed up for.

So when Joe comes in to sign up for another month on 2/1/2004 Sue's
membership that expired on 1/1/2004 isn't counted.

Something like

SELECT Count(*) as NumberOfMembers
FROM MemberShip
WHERE GroupID = [Joe's Group ID]
AND EndDate > [Sign up Date]
AND MemberID <> [Joe's Member ID]

[Stewart Allen]

"rkc" <rk*@yabba.dabb a.do.rochester. rr.nope> wrote in message
news:o8******** ********@twiste r.nyroc.rr.com. ..

"Stewart Allen" <sa****@ThisPar tNotVailid.wave .co.nz> wrote in message
news:bu******** **@news.wave.co .nz...

Membership is an intermediate table between Member and Group so there's a many to many relationship between the two. The rate is assigned to the group

which includes the duration of the amount the group pays and the total
amount of members at that time.

I have a couple of question regarding this.
How do you store the duration when they're still enrolled? e.g.
MemID FName LName
1 Joe Smith
2 Sue Smith
3 Fred Smith
All belong tho the same family so special rates apply.

Joe joined first and the rate is $20.00 per period. Sue joins 2 months

later

and now that rate is $30.00 for both of them. Fred joins 3 months later

and

now the rate is $40.00 for all three of them. 1 month later Sue leaves so the rate reduces back down to $30.00. Joe and Fred are still enrolled at

the

club so how do you store the duration if the membership is still current? If

Joe started 1 year back and the payment periods are monthly, the total
amount that this family is supposed to have paid is $350.00. After 1

more month of membership the total due is $380.00.

With my example above and the Membership table having 2 FKs to create the PK, what happens when the case arises when Sue leaves or is the

StartDate a

FK too?

First, the Group table doesn't need the RateID. It's retrieved from the

Rate table
on an individual basis.

My thought was that the Membership table would track one membership period
for one member. The GroupId would be used to count the number of members
in that group at the time the fee is paid. Every member would be in a
group, even
if that member is the only person in the group. The fee would be based on
the number of people in the Group and the duration of the membership. The
amount due would be taken from the Rate table based on the number of
members and the length of time signed up for.

So when Joe comes in to sign up for another month on 2/1/2004 Sue's
membership that expired on 1/1/2004 isn't counted.

Something like

SELECT Count(*) as NumberOfMembers
FROM MemberShip
WHERE GroupID = [Joe's Group ID]
AND EndDate > [Sign up Date]
AND MemberID <> [Joe's Member ID]

Thanks rkc. I'll have a good look at this and see if I can modify what I've
done.

Here's my current table structure:
**tblMembers**
MemberID (PK)
FamilyID (FK) from table FamilyNames
FName
LName
etc

**tblFamilyName s**
FamilyID (PK)
FamilyName

**tblMembership **
MemberID (FK) from tblMembers
MemTypeID (FK) from tblMembershipTy pes
StartDate
EndDate
The first 3 fields combine to create a primary key

**tblFamilyRate s**
RateID (PK)
FamilyID (FK) from tblFamilyNames
Rate
StartDate
EndDate

**tblMembership Types**
MemTypeID (PK)
MemType

**tblFeesPaid**
FeesID (PK)
FamilyID (FK) from tblFamilyNames
DatePaid
AmountPaid
ReceiptNo

**Relationships **
tblFamilyNames 1-M tblMembers
tblFamilyNames 1-M tblFamilyRates
tblFamilyNames 1-M tblFeesPaid
tblMembers 1-M tblMembership
tblMembershipTy pes 1-M tblMembership

The FamilyRates table records what that family is supposed to pay at any
given period of time. If the EndDate is null in a record that means that
this is the family's current rate. Whenever somebody of this family enrols
or leaves, this table is updated with the EndDate field completed and a new
record created with the new Rate and StartDate. This table doesn't get
touched again until the number of members in this family changes again.

The Membership table records when a member joins and quits. It's on the many
side of the Members because they sometimes rejoins so this needs to be
recorded. If the EndDate field is null, it means that the member is still
enrolled.

When somebody joins they're assigned to an existing family or a new family
is created if they haven't got any brothers/sisters etc that are already
enrolled. Dev's fConcatChild function is used to link all family members
together. This way it's the family that pays the fees not the individual
even if there is only one person that belongs to that family.

What I forgot to mention in my original post was that some families don't
pay on time, maybe miss a couple of payments over 6 months and then start
paying regularly again. It may seem that they're up to date because they're
paying regularly but they are behind.

Finding out the total a family has paid is easy and also the total number of
family members enrolled at any given time period is also easy. What I'm
after is to find out how much the family is supposed to have paid from the
time the first member joined to now when other members of the family have
joined and left which effects how much the family pays at that period of
time. e.g. $20.00 for 1, $30.00 for 2, $40.00 for 3.

Is my table design correct? I'll redesign the whole database if necessary.

Stewart

[rkc]

"Stewart Allen" <sa****@ThisPar tNotVailid.wave .co.nz> wrote in message
news:bu******** **@news.wave.co .nz...

Is my table design correct? I'll redesign the whole database if necessary.

I would never presume I was able to tell you whether your table design is
right or wrong. I don't know all the requirements and if I did I wouldn't
start by designing tables.

Here's a model from a fellow who thinks he is right enough to post his
work on a site called DatabaseAnswers . Maybe you will see some value
in it.

http://www.databaseanswers.com/data_...lubs/index.htm