Is this the right way to organize my database tables?

Question

So I'm making a website that allows users to build contact lists. So their are users, the users have lists, and the lists have contacts. It seems to me that I need 3 tables for this but I just want to make sure.

There would be a User table of course, and then a "List of Lists" table that has the username, and listname, as primary key along with whatever other info we want to attach to the lists as a whole. Finally, for lack of a better word, the List table which would again have the username/listname p.k., then the contact ID and notes and such that the user attaches to that contact on that specific list.

I hope that is a clear explanation. For some reason I feel unsure about this arrangement. For one thing if the website becomes popular the List table could swell to billions of rows. And it also feels a little weird that everybody's list info is all jumbled up in the same table. I suppose I could create separate tables for each user and even for each list but that seems like a bad idea for other reasons.

My db explanation assumes I can use foreign keys on my tables which at the moment isn't actually an option. If I can't get InnoDB tables enabled I will probably use ID's for the lists instead of depending on a compound key. Maybe I should do this anyway?

Answer 1

You do not want to create a table per user. It will be your worst nightmare further down the road. I would have gone for something like this:

User Table
----------
User_ID - Integer - Indexed
Username - VarChar[8]
First_Name - VarChar[30]
Last_Name - VarChar[40]

List Table
-----------
List_ID - Integer - Indexed
User_ID - Integer - Indexed
List_Name - VarChar[30]

List_Contact Table
--------------
List_ID - Integer - Indexed
Contact_ID - [whatever data type it is in the existing Contact table]

User.User_ID=List.User_ID
List.List_ID=List_Contact.List_ID
List_Contact.Contact_ID=Contact.Contact_ID [Linking to the existing Contact table]

If you build in some caching of the record sets, you won't have much of a performance issue even if you got tons of records in the database. After all, all users won't be on the system at once.

Edit: Changed the design to fit the existing Contact table.

Answer 2

That's one way of doing it. You could also have users add contacts and then add tags to their contacts.

Here's what I propose:

User
id
other fields

Contact
id
fk = User.id
tags
notes
other fields

You definitely don't want to start creating new tables for each user, then you'll have thousands/millions of tables and I don't think any DB is designed to be used like that. I'd recommend you get someone who knows more about DB design on your team.

Answer 3

Users(id, username, first_name, last_name)
FriendLists(id, user_id, name)
ListItems(id, list_id, user_id)

Your user table should hold all the basic data about your user. Nothing special here.

Then you have a friend list which consists of an id, a user_id (the list owner), and the name of the list ("best friends", "family", "people i hate").

And then you have your list items, which again have an id (everything should have an id, it just makes it easier to reference), a list id (which is a foreign key to the friend list, which in turn gives you owner), and then the user_id, which is the friend.

So, if Bob is Best Friends with Sally and Joe, then you would have

Users: (0, bob, Bob, Bobberson), (1, sally, Sally, Sallerson), (2, joe, Joe, Joeman)
FriendLists: (0, 0 (bob), "Best Friends")
ListItems (0, 0 (best friends list), 1 (sally)), (0, 0, 2 (joe))