user favorites to a database

how best to structure this type of feature?

[nipear]

We are lauching a new site with around 10,000 documents. What I want to do is allow members of our site select files to put into their favorites list on our site.

My question is how should I structure this to keep the load on my server the lightest? (we will be using SQL).

Should I just create a table with user ids and file ids and then just pull up a list for them by matching their user id to the files they have selected? This seems like it would strain the server and with a lot of users this table could get pretty big.

Or is there a way to do this with cookies?

I'd really like some input on what others have done with similiar situations. Maybe how Brett does it here with the flags?

[brotherhood of LAN]

maybe create your 2 tables, pages and users, as you suggested and index both the id fields.

create a "favouries" table just add both the ids.

I (think) Brett will use regular expressions. Probably have a list of characters next to our userids to note our preferences (or a seperate table), or our ids associated to a thread which is flagged....whichever of the "primary entities" is involved.

Brett's methods will be super-efficient ;) The fact that either one character exists or doesnt exist is probably one of the fastest ways to customise an outcome.

[RossWal]

You can choose a database or cookies; either will work fine. Cookies is simpler, because with the database approach you will need to first assign a user ID (most likely through a sign up page). Once a user has joined you could store their user id in a cookie to prevent them from having to resign in.

There are some downsides with cookies. First, if the user switches PCs their preferences will go away. Likewise if they delete their cookies. Also, some may not have cookies enabled, but IMHO, that's their problem. If they are worried about some cookie voodoo, well that's their problem. Another issues with cookies could be network traffic. The entire list of cookies is sent from the PC to your web server for each page request. If each user has a lot of 'em, and the cokkies are big (ie long document names), and they're using dial-up, well that could be a slowdown. Lastly, if the cookies are to be doled out through a pay-per-doc (vs. a paper doc... sorry couldn't help myself) program, keep in mind theyu are very hackable.

As far as a database goes, size should not be an issue with the numbers you're talking (though you don't say how many docs a user would register for). I would create a 2 column table (users and documents), and index the Users column. You could consider tempporarily stuffing the list of docs into a session variable to minimize data base accesses.

Instead of a database, another server-side datastore option is ldap. That would probably provide faster access than a database, but it could be a bit messy.

All in all, if you can live with the downsides of cookies, that would be what I'd look at. Others would no doubt approach it differently.

Ross