SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    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...

    subscription
    Code:
    - 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!!

    Thanks,


    Debbie

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    how important is history?
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •