How should I structure this database?

Question

Hey,

I am creating a website that will allow people to make lists, but I'm not sure on the best way to store these lists. Should I make a new table for each user, or is that a bad idea? There will be a few columns to each list.

Thanks in advance

Edit: It will be one list per user, although if it's pretty much the same, I may make it multiple lists to give more options in future. Each list will contain the item, a priority, and possibly another column or two. Users will be able to add, edit, and delete items from their list, and make it private or public.

Answer 1

Yes, it's a bad idea to create a new table for each user. Instead, add a UserID column to your List table.

Then you can get a given user's lists like this:

select ListID, Name
from List
where UserID = 42 --example UserID

If the lists have the same schema, then you can create a a single ListValues table with a ListID column that is an FK to the List table, with the columns you require. If each user can create their own columns, then you may want to implement an Entity Attribute Value model.

Answer 2

Its bad idea your list table should something like following.

id list user_id

Answer 3

As Red Filter noted, you should split your info across multiple tables. Your structure depends on what you wish to store in the list, and how users should interact with the lists.. Should one user get to see other users lists? Have multiple lists?

This structure for example lets users have multiple lists, and each list have multiple items:

list
  list_id
  list_name
  creator (user_id)

list_items
  list_id
  item

user_list
  user_id
  list_id