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.