Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2008
    Posts
    2

    Question Simple database design query

    Hi guys,

    Simple question, so i'll try to keep it short.

    2 tables as follows :

    ----------------------------------------------
    USERS (user_id, ip_address, user_agent)
    Primary Key (user_id)

    VISITS (visit_id, user_id, page, time)
    Primary Key (visit_id)
    Foreign Key (user_id) references USERS.user_id
    ----------------------------------------------

    Ok, firstly : Is there anything obviously wrong with this that could have an impact on performance?

    Finally : I've got VISITS.visit_id as a primary key. Would it be better to have the primary key as both VISITS.visit_id and VISITS.user_id?

    Many thanks

    NOTE : For a little background, USERS records the users who visit a website, and VISITS records what page on the site they visit, and at what time.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Do you expect your users to only ever access from one location or do you want to record all IPs?
    Do you expect your users to only ever visit one page or do you want to record all movement?
    The answer to the PK question depends on the answer to the second above question

  3. #3
    Join Date
    Jun 2008
    Posts
    2
    Hi pootle flump,

    Q: Do you expect your users to only ever access from one location or do you want to record all IPs?

    A: I don't expect the users to only ever access from one location. I've got some PHP code that stores a cookie on the user's PC with their 'user_id' in it. It expires after a day, after which they are recognised as a 'new' user.
    --
    Q: Do you expect your users to only ever visit one page or do you want to record all movement?

    A: I expect the users to visit multiple pages on the site, with all movement to be recorded. In other words, each hyperlink clicked within the site will record a new entry into the VISITS table.

    Hope this helps. Thanks!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dark_perfect
    Ok, firstly : Is there anything obviously wrong with this that could have an impact on performance?
    missing index on page, missing index on time

    Quote Originally Posted by dark_perfect
    Finally : I've got VISITS.visit_id as a primary key. Would it be better to have the primary key as both VISITS.visit_id and VISITS.user_id?
    it would be better to do away with visit_id altogether, and declare (user_id,page,time) as the PK
    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
  •