Hi All
I have a DB I'm developing and I need some advice on table development. I'll try keep it as brief as possible.
Essentially the database is for a counseling service. A client attends the service and pays an annual membership. The membership starts from the first date the client attends the service not from the date the membership is paid. It is a non profit service so some clients may not pay all or any of their membership. Membership is $50 per year.
I have a table for Clients (tblClient) which stores all their info. No problem
I've normalised as far as I can and I'm debating whether a separate table should be created for Memberships which is where I need the help.
A client only ever has one membership however the membership expires after a year and needs to be renewed. For each yearly membership there can be many payments up to a total of $50
The client can go through the service more than once in a year.
The time they spend in the service could last more than one year, or they could come back to the service more than a year later either way they would have to renew their membership.
Currently I have:
tblClient:
clClientID - Primary Key
clMemExpireDate
Other Attributes
tblMShipPayment
mpReceiptNo - Primary Key
mpPayDate
mpPayAmnt
mpPaidInFull - Yes/No - I use this to flag if the client has paid $50 in one payment. I also use this in the query to sum every payment not paid in full.
mpNotes
mpClientID - Foreign Key.
Currently I can calculate how much is owing and how much paid but is difficult to display for the user the total, amount remaining how many payments. Also if all payments have not been made any outstanding balance for that year needs to be wiped/zeroed before the next years membership payments begin.
Because I need to know how many payments, the amounts and balance remaining are made on the membership just for that year I'm thinking I need a separate table for membership then linked to membership payments. However alot of the fields can be calculated and I'm wondering if they warrant even being stored in the table.
I'm thinking I should have:
tblClient:
clClientID
Other Attributes
tblMembership:
msMShipID
msDateStarted - equal to the the first date the client is seen. I have another table to record this called tblServiceHistory
msDateExpired - Always 1 year from msDateStarted. Could be calculated but required I think to store in the table.
msTotalPaid - Sum of payments made for this annual membership. I have a query that calculates this already.
msTotalOwed - Calculation, 50-msTotalPaid
msStatus - Current or Expired
msClientID - Foreign Key
tblMShipPayment:
mpReceiptNo - Primary Key
mpPayDate
mpPayAmnt
mpPaidInFull - Yes/No - I use this to flag if the client has paid $50 in one payment. I also use this in the query to sum every payment not paid in full.
mpNotes
mpMShipID - Foreign Key.
Which would result in:
Client has many renewals for one membership
For each renewal there can be many payments
Does this sound correct or am I just over thinking it?
Many thanks for any help and taking the time to read, I hope I have been concise enough.
All help is greatly appreciated.
I have a DB I'm developing and I need some advice on table development. I'll try keep it as brief as possible.
Essentially the database is for a counseling service. A client attends the service and pays an annual membership. The membership starts from the first date the client attends the service not from the date the membership is paid. It is a non profit service so some clients may not pay all or any of their membership. Membership is $50 per year.
I have a table for Clients (tblClient) which stores all their info. No problem
I've normalised as far as I can and I'm debating whether a separate table should be created for Memberships which is where I need the help.
A client only ever has one membership however the membership expires after a year and needs to be renewed. For each yearly membership there can be many payments up to a total of $50
The client can go through the service more than once in a year.
The time they spend in the service could last more than one year, or they could come back to the service more than a year later either way they would have to renew their membership.
Currently I have:
tblClient:
clClientID - Primary Key
clMemExpireDate
Other Attributes
tblMShipPayment
mpReceiptNo - Primary Key
mpPayDate
mpPayAmnt
mpPaidInFull - Yes/No - I use this to flag if the client has paid $50 in one payment. I also use this in the query to sum every payment not paid in full.
mpNotes
mpClientID - Foreign Key.
Currently I can calculate how much is owing and how much paid but is difficult to display for the user the total, amount remaining how many payments. Also if all payments have not been made any outstanding balance for that year needs to be wiped/zeroed before the next years membership payments begin.
Because I need to know how many payments, the amounts and balance remaining are made on the membership just for that year I'm thinking I need a separate table for membership then linked to membership payments. However alot of the fields can be calculated and I'm wondering if they warrant even being stored in the table.
I'm thinking I should have:
tblClient:
clClientID
Other Attributes
tblMembership:
msMShipID
msDateStarted - equal to the the first date the client is seen. I have another table to record this called tblServiceHistory
msDateExpired - Always 1 year from msDateStarted. Could be calculated but required I think to store in the table.
msTotalPaid - Sum of payments made for this annual membership. I have a query that calculates this already.
msTotalOwed - Calculation, 50-msTotalPaid
msStatus - Current or Expired
msClientID - Foreign Key
tblMShipPayment:
mpReceiptNo - Primary Key
mpPayDate
mpPayAmnt
mpPaidInFull - Yes/No - I use this to flag if the client has paid $50 in one payment. I also use this in the query to sum every payment not paid in full.
mpNotes
mpMShipID - Foreign Key.
Which would result in:
Client has many renewals for one membership
For each renewal there can be many payments
Does this sound correct or am I just over thinking it?
Many thanks for any help and taking the time to read, I hope I have been concise enough.
All help is greatly appreciated.
