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?

Any help would be greatly appreciated.

Cheers,

James.

yes, it is (but please note, you chose the adjective, i didn't )

no, it wouldn't -- declare an index on username

Thanks for your help.

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...

Thanks again.

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.

If not, I can always choose a 64-bit value to auto-increment perhaps. It'll mean a major bit of XUL/javascript rewriting though...

Thanks

what is your understanding of the purpose of an auto-incrementing column?

no, i'm being serious

i wouldn't want you to go off writing code to do something that has no purpose

ok, I didn't make myself very clear. I must sound a bit dense now...

I can use auto_incrementing to have a unique ID for each bookmark, to be used as a key. The reason I wanted to have a unique ID only within each user is that I'm worried about the number of bookmarks being very large. With several thousand users ( hopeful...), and several thousand bookmarks each I could eventually get to such a large number that it goes 'around the the clock and back to zero' - or more likely - that the figures get too large for javascript to cope with as an integer.
Now I can hear you thinking that say 10000*1000 = 10 million - so where's my problem? The users can delete bookmarks - they're in constant use so the 'ids' will carry on creeping upwards. I could set to BIGINT perhaps, but that doesn't solve javascript not being able to cope with it. (I'm looking up maximum integers in javascript now, not sure what the figure is - my guess is 4 billion - 32bit).

Ok, so maybe I'm just thinking too far ahead...

EDIT: Actually, there's no reason why javascript can't just deal with the ID as a string - no manipulation will be required. So maybe set it to BIGINT to be safe, and change JS code to treat it as a string.

i know i'm going to hate myself for asking, but what's javascript got to do with it?

he he.

Well, I'm writing a Firefox extension, which is written in XUL/javascript. The extension accesses the database data through PHP/MySQL. So when I retrieve bookmarks for a user for example, it calls the PHP script - which accesses the database - and returns a text file with all the relevant data.

If you want more info, go to http://www.mozmarks.com/
Not finished yet (obviously).

"With mozmarks, there is need to even synchronise, ..."

insert the word "no" before "need"



i wish you good luck, it looks like a great project

i personally can't use it because all my bookmarks go into Powermarks (by kaylon.com)

by the way, INTEGER in mysql goes up to 2billion (4 if you use UNSIGNED)

if you're feeling ambitious, consider capturing unique bookmarks, like furl does, and link common ones to multiple users

it's good that you're thinking far enough ahead to be worried about whether javascript can handle BIGINTs

however, i would suggest that you simply go ahead with INTEGER

by the time you actually reach 2 billion, you will need to redesign your database anyway (for other reasons), so there's no use crippling your app (like, handling a BIGINT as a string) in the meantime

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.