SQL Table Design Help for Payments of Membership Subscriptions

I开发者_JAVA技巧 am planning/designing a MSSQL database for a golf club. I have the following tables:

Members – standard contact information MembershipTypes - Full, Junior, Over 60’s etc.. PaymentMethods – Cash, Standing Order, Cheque etc..

One of the table designs I am struggling with is payments. Each January the subscription amount will change, for example in 2010 the Full Members subscription is £1000 for the year, so if Member A is a “Full Member” he can pay the £1000 upfront in one payment or instalments over 10 months.

This part is not a problem. I could have a payments table with PaymentID, MemberID, Date, PaymentAmount and this could tell me how much the member has paid to date and how much is outstanding.

The issue for me is in January 2011 the Full Members subscription could increase to £1100 which would create a problem when trying to do calculations. In theory I would need to archive the 2010 payments and start fresh for 2011 – I don’t want to do this as I would like to show a history of every single payment the member has ever made. I welcome any suggestions for the most efficient table design for this scenario.

---

I believe you need a variable subscription amount at the member level. For example, if I sign up and you give me a discount for helping you with the design, the amount you charge me should be located at the member level along with a subscription date so you know how much to collect, monthly or all at once. When my subscription is about to expire (either sliding or static), I should be charged the base rate which would be the new rate. The math is quite simple at this point because you know when I signed up, how much I've paid to date and how much I owe (prorated or sliding).

Here's a basic example:

Member
    MemberId
    Name
    Address
    Etc...

Product
    ProductId
    Name
    Description
    Price

Payment
    PaymentId
    SubscriptionId
    Amount
    DatePaid

Subscription
    SubscriptionId
    MemberId
    ProductId 
    Price
    StartDate
    EndDate (if needed)

Untested queries, but very close:

*-- how much do I owe?*
select m.Name, sum(s.Price) - sum(p.Amount) as Owes
from Member m
join Subscription s on m.MemberId = s.MemberId
join Payment p on s.SubscriptionId = p.SubscriptionId
where m.MemberId = 1

*-- how long have I been a member*
select datediff('year', min(StartDate), getdate()) as yrs,
    datediff('month', min(StartDate), getdate()) as mths,
    datediff('day', min(StartDate), getdate()) as dys
from Subscription 
where MemberId = 1

*-- when does my subscription expire*
select max(EndDate) as ExpirationDate
from Subscription 
where MemberId = 1

*-- list all payments by product*
select m.Name as MemberName, 
    pr.Name as ProductName, 
    pr.Price as ProductPrice,
    s.Price as SubscriptionPrice,
    p.Amount as AmountPaid
    p.PaidDate,
from Member m
join Subscription s on m.MemberId = s.MemberId
join Payment p on s.SubscriptionId = p.SubscriptionId
join Product pr on s.ProductId = pr.ProductId
where MemberId = 1

---

I'm thinking that you need to create a table called membership costs where the membership fees per year are recorded with a unique ID that stipulates the fee that each type of member must pay per year. This should enable you to allow members to make payments in reference to a particular "product".