Simple database design query

dark_perfect · #1 (**permalink**) 06-04-08, 06:33

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.

pootle flump · #2 (**permalink**) 06-04-08, 06:38

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

dark_perfect · #3 (**permalink**) 06-04-08, 06:46

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!