One large database table for my "list" or a new table for each "list"

I'm trying to create a database system where users can create lists, and their friends (that they allow list access to) can add to the list.

I'm trying to map out this schema and can't decide between the following:

List Table With attributes: listid, entry-number, entry, user-id

Where listid is the list being changed, entry-number is the number of that entry in the list (so the first item in a list is entry 0), entry is the entry on the list, and user-id is the user who added the entry

VS

Specific List Table where a specific table is made for eac开发者_C百科h list with attributes entry-number, entry, user-id

It seems like the 2nd option makes it much easier to get information/change a list once we find the table, whereas the first one is much easier to understand.

I'm just getting into databases so I want to pick the schema correctly.

Thanks!

The first option is more maintainable and normalised. It would be easier to query this option and create applications that use the list.

From what I can see, you should have two tables. One that holds user information and one that holds list information. Users will have userID (PK) and your List table would have listID (PK) and a userID as FK to reference which user the list belongs to. So yeah, your first choice :) GL

For example:

User
userID(PK) | username | etc
--------------------------
1 | Bob | etc
2 | Nick | etc


Lists
listID(PK) | userID(FK) | date_entered | entry
----------------------------------------------
1 | 2 | 1/2/2011 | blah blah
2 | 2 | 2/1/2011 | blah blah
3 | 1 | 2/3/2011 | blah blah
4 | 2 | 2/6/2011 | blah blah
5 | 1 | 3/1/2011 | blah blah

//you should know the userID for the user you are looking up list info for (C#)
query = "select * from Lists where userID = " + userID.ToString() + " ORDER BY date_entered DESC";