January 3, 2010 at 4:52 am
I'm trying to start a db that shows when someone has been a continuous member for 10 years. So if they have been a member for 12 years but didn't renew their membership 7 years ago. How can I show that they've only been a continuous member for 6 years.
I hope this makes sense.
Kris
January 3, 2010 at 2:23 pm
I think that this probably comes down to two general design questions:
1) Data Representation (how should this be recorded in the database), and
2) Data Presentation (how should this be shown to the users of the db).
As to the first, what you do not want is for the Members' membership history to be in the same table as the Member's info. So at a minimum you need two tables:
Members: (ID, Name, Membership#, Address, etc...)
---------
and
MembershipLogs: (Membership# (or Members.ID), ...etc...(see below) )
----------------
The second thing to decide with Data Representation, is whether the MembershipLogs records each represent a single membership "event", or whether each one represents a membership "period". They would look like this:
MembershipLogs(periods): (Membership#, BeginDate, EndDate(nullable), Chapter, Level, etc...)
-------------------------
or
MembershipLogs(events): (Membership#, EventType, EventDate, Chapter, Level, etc...)
-------------------------
The first appears to be easier to deal with and it is, at first. But eventually it often happens that you accumulate more and more requirements to represent more and different "things" in the MembershipLogs, and you start to realize that the "periods" design is only really good at capturing the start and finish of membership periods, more subtle things, like changes in status other than termination, Chapter changes, Level changes, etc., are not easily handled by it.
The "events" design looks like more work, and it is at first. It's real strength is that it is much more flexible and easily adaptable to additional or even changed requirements. For instance, everything that I mentioned before can be readily handled by it, with few, if any changes.
---
As to the second design question (Data Presentation), that is something that you should think about and let us know more about your needs.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply