Need help with Subscription table

Databases
1

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…

subscription


- id (PK)
- article_id (UK)
- member_id (UK)
- started_on
- ended_on

A record would be INSERTED when a Member subscribed to an Article and unsubscribed if the “ended_on” was Not Null.

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!! :smiley:

Thanks,

Debbie

2

how important is history?

if a member unsubscribes from an article, why does it matter whether there’s a record of it?

if a member re-subscribes to an article that he isn’t now but was once already subscribed to, what difference does it make to anything?

what’s the id for? why couldn’t you just use the UK as the PK?

3

I’m not sure.

Besides the obvious use of the table and keys to determine who gets notified, I am thinking I might want it for analysis, but I really don’t know.

It might be interesting to build a query/report that tells me the following…

* Of those who subscribed to an Article, how long did they stay “subscribed”?

  • Did they stay “subscribed” indefinitely?
  • Did the “unsubscribe” after a period of time?

(Optional)
If the “re-subscribed”…

  • How long did they stay “subscribed”?
  • Did the “unsubscribe” after a period of time?

if a member unsubscribes from an article, why does it matter whether there’s a record of it?

I think it would be useful to know how long people stay engaged in an Article Thread.

If people subscribe and then unsubscribe right away, it might tell me…

  • The conversation was boring
  • The Member didn’t find value talking with others
  • The “mood” of the conversation wasn’t conducive to making the person stay (Maybe there was lots of spammers, trolls, etc?)

if a member re-subscribes to an article that he isn’t now but was once already subscribed to, what difference does it make to anything?

See above.

Maybe I want to keep track of each “engagement”.

(Personally, I can’t see people “re-subscribing” very often, but if I have reporting on this, then over-writing it might give me inaccurate results?)

what’s the id for? why couldn’t you just use the UK as the PK?

I tend to throw in an “id” auto-increment by default, because it can come in handy later. For example, maybe I build a “Subscription History” table off this initial table, and I find it is easier to work with a singular “id” PK. Although I don’t anticipate such a need right now.

Thanks,

Debbie