Many to Many relationship ???

[Sebbers]

Hi,

Fairly new to database design so here goes with my attempt to design a membership database:

The business rules are as follows:

1) A product must have 1 or more fees but not all products have the same fees:
1.1) A "7-Day Adult membership" (product) member pays a 7-Day Adult subscription fee, insurance fee, union fee and a catering fee.
1.2) A "7-Day Child membership" (product) pays a 7-Day Child subscription fee, insurance fee and a union fee.

PRODUCT Table
ProductID ProductName
1 7-Day Adult Membership
2 7-Day Child Membership

FEE Table
FeeID FeeName
1 7-Day Adult Membership Subscription Fee
2 7-Day Child Membership Subscription Fee


3 Insurance Fee
4 Union Fee
5 Catering Fee

PRODUCTFEES Table
ProductID FeeID1 1
1 3
1 4
1 5
2 2
2 3
2 4

When we assign a product to a member, I insert the corresponding product into a MEMBERPRODUCTS Table (a member can have more than 1 product - ie a locker) but not sure if this is the right way to do this. I am also contemplating on inserting the corresponding fees for each product into a MEMBERPRODUCTFEES table but again not sure if this is the right way to go about this.

My dilemna is the following:
The Club may, from time to time, want to charge a joining fee for new members (but we don't want to charge the existing members).
If I add the joining fee to the FEE Table, it doesn't really have an associated product and therefore my many-to-many relationship does not work. Ideally, it needs to be in the MEMBERPRODUCTFEES table, this way I can pick and choose which members get charged this fee.

I'm probably over-complicating things but would welcome some guidance.

Many thanks.

[Rainlover]

I really have a lot of difficulty reading all uppercase. I know others do as well. Something to do with getting old I guess.

Table Names should be preceded with tbl. eg tblProducts, tblMembers.

This is nit picking I know. But if you were to fix this people might find it easier to read your question. If you do some searching you should find some "Naming Conventions" If you can't then post back and I will send you mine.

The keeping of pervious fees charged can become involved, depending on what you want to do. I see this as your main issue not the Many to Many problem.

Can you explain in plain English the rules to keeping payment history.