Unanswered: tables and keys for user bookmarks database (was "Pretty basic DB design problem")
I'm a newbie to databases - well, large ones anyway.
I'm trying to design a database (in MySQL) which stores 'bookmarks' for users in a hierachical fashion. I've figured out how to do that for one person (adjacency list, in one table). So for multiple users, I have one table to store user details - and one table for each user (named user_<username>).
My question is, is that a dumb way of designing a database - having one table for each user? Will I encounter problems? Certainly phpMyAdmin might struggle with thousands of tables.
Obviously, I could place all the tables into one, and have a 'username' field and to a 'WHERE username=...' but that would be horribly inefficent wouldn't it? I'm expecting possibly thousands of users...
I'd rather have it as one table though, since if I add a field at the moment I'd have to change ALL the tables (except users table, of course).
Is there a smarter way of doing it, instead of having a table for each user?
I did think of the 'index' option - I should have mentioned it in my question. I suspected that that was less efficient that separate tables - but you've just shown how little I know about databases. Damn, I did a course on databases when I was at university - this shows how little I remember...
I used to have an auto_increment-ing value for each table - so that each user's bookmarks would have a unique ID.
All of my users are now in the same table, indexed by 'username'.
Now the auto_increment value will create an ID that is unique for the whole table, instead of unique to only that user's bookmarks. Is there a way of associating the auto_increment-ing with only a particular 'username', so that it increments only when an INSERT operation is carried out on that username. IE, a different auto_increment value for each username. Doing it manually would be pretty difficult with the possibility of simultaneous operations on the same username.
ok, I didn't make myself very clear. I must sound a bit dense now...
Ok, so maybe I'm just thinking too far ahead...
Last edited by dcs3jah; 02-28-05 at 15:48.
Fixed the typo on the web page - it's only a holding page really.
Not sure I'm feeling ambitious enough to capture unique URLs. Not worth the effort I don't reckon.
You're right about probably needing a DB redesign after a while I'm sure. MySQL might even begin to buckle under the strain of that many users- I don't know. Maybe I'll find out if it ever becomes popular.
Thanks for all your help - I really appreciate it.