Membership products data schema

Question

I'm trying to model my organization's membership products for accepting and recording membership purchases in our business database. Someday we hope the purchases will be made online and automatically put into the business database.

The problem is that our membership products are all over the place. We have 4 types of organizations that can join our organization. There is a separate price schedule for each organization type. Attributes of the organization determine the price they will pay from the schedule (if their revenue is between $2mil and $5mil they pay $2000, for example). The attributes that are used in each price schedule are different. For example, businesses pay according to their annual revenue while schools pay according to their full-time equivalent student enrollment - which we calculate by having school members provide us their full-time and part-time student enrollment #s). To make it more complicated, there are discount programs (for example, 15 months for the price of 12 for early renewals, discount for under-resourced schools, etc...). Then, members have the option to buy multiple terms at a time and we sometimes allow people to buy pro-rated partial terms (month is the smallest unit.)

And then on top of that, I'm now supposed to make the product and transaction data schema generic enough that it can be the data source of record for all purchases - for example, event registrations and program enrollments in addition to membership purchases.

Help!! I've been told this is standard commerce data schema stuff, but every sample data schema I've been able to find on line couldn't do the above, at least as far as I can tell. Has anybody seen a schema that could do this sort of thing? If you have, could you share it with me?

Any other leads would be greatly appreciated as well. Thanks!

Oh, I forgot to mention that there are actually 3 price schedules for the business organization type because they can choose between 3 levels of involvement. — , Jul 29 '09 at 23:53
I also forgot to mention that the membership price schedules and categories change at least every 12 months. If I were to define all the permutations of all the pricing options as individual products, there'd be hundreds of products for membership alone! — , Jul 29 '09 at 23:57
It's also worth noting that for memberships at least, I'm never asked to answer the question "how many members purchased X membership product last month or last year." Instead, I'm asked to answer "how many members of x type joined last month and how much membership revenue did they bring in?" So, my initial instinct was to just record purchases and leave the product definitions and price schedules in the membership application forms - since the only questions we ask of the DB are about the term, start date, dues amount and type of member. — , Jul 29 '09 at 23:59

score 1 · Answer 1 · answered Jul 30 '09 at 06:47

It seems you need to separate the business logic from the data design. Try thinking of the simplest data constructs that apply to this matter, and lay on top of them the logic of the memeberships.

For example:

CREATE TABLE payment(
     memeber_id INT,
     payment INT,
     expected_date DATE,
     payment_date DATE);

 CREATE TABLE credit(
     memeber_id INT,
     credit INT,
     last_movement` INT,
     next_movement INT,
     last_movement DATE,);

 CREATE TABLE member(
     id INT,
     membership_plan INT,
     ...)

On top of this, for example, you can quite easily query whether the member has credit, left, have he paid whatever was expected, etc. The business rules are set in software, on top of this data. For each membership plan, there should be rules governing it differently.

You can also add one more general Entity-Atribute-Value table to support these plans, if you plan on building the business logic in a more configurable fashion. To the extreme, each member can even have overrides on this table.

CREATE TABLE member_attributes(
         membership_type INT,
         attribute VARCHAR(30), --for example 'Monthly Pay', 'Membership duration'
         value INT)

Membership products data schema

1 Answers1