Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Dec 2004
    Posts
    55

    Unanswered: tracking member views of clan pages (was "table design help needed")

    I am helping a friend with a gamming website. There are pages which displays data from other clans.

    Members need to register and login to view full clan details. User who have not logged in can only view partial data about a clan.

    I need to keep track of the kind of hits each page received. That is I want to tell say "Clan X" that these particular members viewed your page these many times and on these dates and these members who have not registered have viewed your page these many times and on these dates.

    I am using ASP with MS SQL.

    I would like some help on designing the table layout that is efficient for keeping track of the visitors for each page.

    Any help will be appreciated.
    Last edited by oldie123; 02-28-05 at 21:09.

  2. #2
    Join Date
    Dec 2004
    Posts
    55
    This is the design I currently have in mind

    ----------------------------------------------------------------------
    clanID | memberID | memberName | ClanName| dateViewed | UserType |
    ----------------------------------------------------------------------
    | | | | | |
    | | | | | |
    | | | | | |
    ----------------------------------------------------------------------

    clanID = the id clan who page was viewed
    memberID = is the ID of the member who viewed the page
    memberName = is the name of the member who viewed the page
    ClanName = is the clan name of the member who viewed the page
    dateViewed = Date the page was viewed
    UserType = Registered/Unregistered

    Will the baove table be a problem?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    couple points for you to ponder

    if a member views some pages, and some rows in this table get created, and later that member changes his clan, you now have what's called a "data anomaly" unless you specifically intended to track the member's clan at the time of viewing, disregarding what that member's current clan might be

    also, how do you record the memberid of an unregistered viewer?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2004
    Posts
    55
    Thanks for your reply. You got some really good points.

    This is the first time I am working on such a thing.

    if a member views some pages, and some row in this table get created, and later that member changes his clan, you now have what's called a "data anomaly" unless you specifically intended to track the member's clan at the time of viewing, disregarding what that member's current clan might be
    I did not think of this.

    If I only store "clanID, memberID, dateViewed, UserType", then while generating reports getting individual reports will be time consuming since I need to go through each member record.

    But if I store "clanID, memberID, memberName, ClanName, dateViewed, UserType" then everytime a person updates his info, the logs table can be updated to reflect this change.

    If a member account is deleted, I have the member data, but the ID will be invalid. Will this be a problem?

    How can I avoid these problems?

    also, how do you record the memberid of an unregistered viewer?
    Was planning on keeping this -1. Will this be a problem. What would be a problem to avoid this?

    If you have a better solution, can you please help me out with it.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oldie123
    If I only store "clanID, memberID, dateViewed, UserType", then while generating reports getting individual reports will be time consuming since I need to go through each member record.
    no, it will actually be very efficient, you just need to use a JOIN to the member table, and make sure the log table memberID column has an index

    you will also save space (by not storing membername and clan on every log row)

    however, as i suggested earlier, if a member changes clans, you would no longer have the original clan in the log row

    If a member account is deleted, I have the member data, but the ID will be invalid. Will this be a problem?
    the way to deal with this is to "deactivate" (not delete) the member

    Was planning on keeping this -1.
    use NULL instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2004
    Posts
    55
    Thanks again Rudy.

    no, it will actually be very efficient, you just need to use a JOIN to the member table, and make sure the log table memberID column has an index
    I'll go with clanID, memberID, dateViewed, UserType. How do I create and use the index?


    you will also save space (by not storing membername and clan on every log row)
    thats true

    however, as i suggested earlier, if a member changes clans, you would no longer have the original clan in the log row
    Actually if a member changes clan it won't affect the log. I am sorry if the previous explaination wasn't clear. I hope the below explaination helps.

    clanID = the id assigned to the clan who's page was viewed.
    memberID = is the ID of the member who viewed the page
    dateViewed = Date the page was viewed
    UserType = Registered/Unregistered

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you would like to know how to create an index, allow me to suggest that you consult the manual at mysql.com

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2004
    Posts
    55
    CREATE INDEX memberIndex
    ON memberLog (memberID)

    After creating the memberLog table I use the above statement to create a memberid index.

    To then retrieve their info I use the below query

    SELECT clanID, memberID, dateViewed, UserType, Username = (SELECT memberName FROM members WHERE members.memberID = M.memberID)
    FROM memberLog M

    Is this right? How does the index get used? I tried looking on google on how to use the index, but was unsuccessful.

    Any help will be highly appreciated.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the join you want is like this --
    Code:
    select L.clanID
         , L.memberID
         , L.dateViewed
         , L.UserType
         , M.Username 
      from memberLog as L
    inner
      join members as M
        on L.MemberID
         = M.MemberID
    the index on memberlog(memberID) helps with the efficiency of the join

    the member table presumably has memberID as primary key so it already has a index
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Dec 2004
    Posts
    55
    the member table presumably has memberID as primary key so it already has a index
    The memberID on the member table is unique but is not a primary key(a stupid mistake on my part). Should I make it a primary key or should I just create an index for it?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    make it a primary key

    this ensures that it's unique and gives it an index at the same time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Dec 2004
    Posts
    55
    thanks a lot

  13. #13
    Join Date
    Dec 2004
    Posts
    55
    select L.clanID
    , L.memberID
    , L.dateViewed
    , L.UserType
    , M.Username
    from memberLog as L
    inner join members as M on L.MemberID = M.MemberID
    The problem with the above query is that if memberID is NULL then the record will be skipped. So unregistered members are not shown.

    Is there anyway to avoid that?
    Last edited by oldie123; 03-02-05 at 17:07.

  14. #14
    Join Date
    Dec 2004
    Posts
    55
    SELECT L.clanID
    , L.memberID
    , L.dateViewed
    , L.UserType
    , Name = (SELECT member_name FROM members WHERE L.MemberID LIKE MemberID)
    FROM memberLog as L
    The above code works fine. Is this a bad query?

    Also since memberID's are not integers but strings, should I use "L.MemberID LIKE MemberID" OR "L.MemberID = MemberID"?

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select L.clanID
    , L.memberID
    , L.dateViewed
    , L.UserType
    , M.Username
    from memberLog as L
    left outer join members as M on L.MemberID = M.MemberID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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