Structuring a database

[dcs3jah]

I'm a newbie to databases - well, large ones anyway.

I'm trying to design a database which stores 'bookmarks' for users in a hierchachical 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 talbe for each user (named user_<username>).

My question is, is that a dumb way of designing a database - having one table for each user.

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.

Any help would be greatly appreciated.

Cheers,

James.

 

[azzazzello]

Ok...if I am understanding you correctly, you need 2 tables. One for the userS (not user) and one for their data. You will have a unique key linking them them, so you can pull data based on it. Here is an example

user_table

id | name | username
----|--------------------|---------|
1 | Azzazzello | Azzie
2 | Woland | Messier


bookmark_table

record_id | user_id | bookmark
-----------|-----------|-----------
1 | 1 | ...
2 | 1 | ...
3 | 2 | ....
4 | 1 | ....

There are 2 users, Azzazzello has 3 bookmarks, Woland has 1. The *id* field in user_table corresponds to *user_id* in bookmark_table. So, say, if you wanted to pull out all bookmarks of Azzazzello, you would do

SELECT bookmark_table.* FROM bookmark_table,user_table WHERE username = "Azzie" AND user_id = id;

 

[dcs3jah]

Thanks for the reply azzazzello.

I realise I can do that, but I was worried that it would be very inefficient once there are thousands of users in the table.

However, I found out that I can set 'username' to be indexed, and therefore it won't be too inefficent.

I'll get to work on it!

Thanks,

James.

 

[azzazzello]

Oh yes! I meant, and forgot, to write that if you index the search table, you will get much faster response. However, I think that, if you follow the example above, you will need to index the *id* table, and not the username table, because that is the one that will be cross-referenced. As for efficiency, I had 2 tables which I crossreferenced in a sub-select. One had 3,000 entries, the other 9,000. Query took 58 seconds. When I indexed the ID field, query took 0.17 seconds. So yea, it will be more efficient just a tad