design for fitness club

[Guest]

I'm making a database for a fitness club. The aim is to record all the
clients and how much they owe and to record payments.

The difficulty is that there are all different ways of paying - some pay
monthly but then it's a bit more expensive, some pay yearly some pay
quarterly. The monthly clients sometimes don't come the whole month so they
ask not to be active member for that month and they don't have to pay.

Also there are holiday specials that a registered user and old user can take
advantage of.

What tables should i have and what fields.

 

[Guest]

Here is a starter set. You need to relate them.
Member --
MemID - Autonumber
LName - Text
FName - text
MI - text
M-F - Text
Join - Date
BDate - Date

Attendance --
MemID - Number - Long Interger
ARR - DateTime
DEP - DateTime
CMTs - Text

Complaint --
CompID - Autonumber
Complaint - Text

MemComp --
MemID - Number - Long Interger
CompDate - Date
CompID - Number - Long Interger
Severity - Number - Long Interger

Payment
MemID - Number - Long Interger
Paid - Date
Amt - Currency
PdThru - Date
CMTs - Text

 

[Guest]

Thanks i get the general idea so as i mentioned before there are different
payment methods so say for year 1 mr x decideds to pay monthly and for year 2
he decides to pay quarterly. So basically i'll have a members table with all
relevant info as you've put below then the status table will include
Status ID - autonumber
Status Name - text
Amount to pay per status - currency
How many months it covers - number

Then i will have a status chosen table
status id - number
member id - number
date status changed - date

Is that correct?

 

[Guest]

Sounds good except I do not see the need for the "How many months it covers"
unless you are giving discounts if they pay an annual payment up front.

 

[Guest]

THats the problem, if they pay monthly it's most expensive then quarterly and
yearly is the cheapest - so should i have a separate months table? how should
it work?

 

[Guest]

The status table I put should do it. Create a list of status. Use a two or
three part status code - Numerical for payment increment (per visit, monthly,
quarterly, semiannually, & annual) and alpha for longivity (0-1 yrs, 1-2,
2-5, 5-10, etc). Use number in third place for specials (off season for
monthly, etc).

 

[Guest]

Thanks but what do you mean a 3 part status code. In the status table what
sould the field headings be - status id, amount, amount of days - is that
what you mean
what do you mean alpha for longivity?

Thanks!

 

[Guest]

You could use 1A (2 part) or 1A1 (3 part) or use separate fields for payment
frequency, how long they have been a member, and infrequent/specials.

 

[Guest]

So do you mean e.g the annual membership costs $125 monthly costs $15 and
holiday specials cost $20 so should the status code (i.e ID?) for each type be

Annual125365
Month1530
Holiday1420

I'll explain annual one. Part 1 annual part 2 125 cause that's how much it
costs and part 3 365 because thats how long it lasts? Is that right? Can the
computer recognise it as a 3 part code or is it just for me to know.

Also will this enable that anyone can change there status at any time and
not loose their old status?

Thanks for all your help!!

 

[Guest]

You are still missing it. Use a combination of payment status and longevity
for pricing.

Longevity Years % Discount
A 0 - 1 0
B 1 - 2 2
C 2 - 5 5
D 5 - 10 10

Status Category Premium A B C D
1 Per Visit $5.50 $5.50 $5.39 $5.23 $4.95
2 Weekly $25.00 $25.00 $24.50 $23.75 $22.50
3 Monthly $100.00 $100.00 $98.00 $95.00 $90.00
4 Quarterly $275.00 $275.00 $269.50 $261.25 $247.50
5 Semiannual$525.00 $525.00 $514.50 $498.75 $472.50
6 Annual $985.00 $985.00 $965.30 $935.75 $886.50