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?
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 First_Name - VarChar Last_Name - VarChar List Table ----------- List_ID - Integer - Indexed User_ID - Integer - Indexed List_Name - VarChar 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.
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))
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:
fk = User.id
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.