Need help with Subscription table
I would like to add a feature to my website where Members can "subscribe" to an Article Conversation and follow when other Members add new Comments.
My original plan was to have this...
A record would be INSERTED when a Member subscribed to an Article and unsubscribed if the "ended_on" was Not Null.
- id (PK)
- article_id (UK)
- member_id (UK)
Seems simple enough, except for these issues...
What if a Member "subscribes", then "unsubscribes" and then comes back and decides to "subscribe" again?
Should I just over-write the existing record?
Should I add a 3rd composite-key (i.e. "started_on")?
Would it make sense - from a Reporting standpoint - to keep track of this activity?
If I am over-writing the first time the Member subscribed, I would be messing up my reporting. (It might be nice to know "How long was a Member Subscribed?" even if that is a couple of times. Then again, maybe it would be uncommon for a person to subscribe/unsubscribe/subscibe/etc...)
In closing, I don't have enough experience with something like this to make a wise design decision, and could use some other people's thoughts.
I always lean to "over-building" solutions, because you almost always will need more as things grow. At the same time, though, I don't want to add a lot of complexity where it is not needed.
Please enlighten me!! :D