I am developing a system to process recurring billing for members. Items to be purchased can be grouped together for a special package rate, or purchased individually for a higher, stand-alone rate. The portion of my database schema that determines the amount to be paid for recurring items consists of the following 4 tables:
MEMBER_RECUR_GROUP
member_id (PK)
recur_group_id (PK)
RECUR_GROUP
recur_group_id (PK)
RECUR_GROUP_ITEM
recur_group_id (PK)
recur_item_id (PK)
recur_amount
RECUR_ITEM
recur_item_id (PK)
item_code
recur_term
Items are attached to recur groups and associated with members. The same item can appear in multiple recur groups for a given member.
For example, let's say we have 3 items: apple, orange, and banana. There can be a recur group containing all 3 (individual item prices in this group would be the cheapest). There can also be a recur group for just 2 items, say orange and banana. And there can be a recur group for just apple (it's stand-alone price would higher than it's "packaged" price within the other groups), another for just orange, and another for just banana.
Given a member_id and one or many item_codes (probably a CSV, something like "apple, banana"), I would like a query to return the recur_term and recur_amount for each item_code from the recur_group that contains all of the item_codes. If there is not a recur_group that contains all of the items, then the recur_term and recur_amount values from the stand-alone recur_group for the individual items should be returned.
I hope I've explained this clearly, but it's not the easiest thing to verbalize. If you have questions on the setup or desired outcome, please ask in the comments and I'll clarify. I am using SQL Server 2005 if implementation matters. Thank you!
@Martin, here is the sample data. (I am back on the original computer I posted from now.)
MEMBER_RECUR_GROUP
1, 1
1, 3
1, 4
1, 5
RECUR_GROUP
1
2
3
4
5
RECUR_GROUP_ITEM
1, 1, 1.00
1, 2, 1.00
1, 3, 1.00
2, 2, 1.50
2, 3, 1.50
3, 1, 2.00
4, 2, 2.00
5, 3, 2.00
RECUR_ITEM
1, apple, 3
2, orange, 3
3, banana, 3
If member ID 1 is auto-billed for apple, orange, and banana, the pricing for recur group ID 1 is used.
If member ID 1 is auto-billed for apple and orange, the combined pricing for recur group ID 3 and 4 is used. (Because member ID 1 doesn't have a recur group with apple and orange.)
If member ID 1 is auto-billed for orange and banana, the pricing for recur group ID 2 is used.
If member ID 1 is auto-billed for apple, the pricing for recur group ID 3 is used.
If member ID 1 is auto-billed for orange, the pricing for recur group ID 4 is used.
If member ID 1 is auto-billed for banana, the pricing for recur group ID 5 is used.