CODEWITHSUNDEEP
databaseschema
mamontazeri
mamontazeri

Reputation: 319

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 each 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!

Upvotes: 1

Views: 91

Answers (2)

Nick Rolando
Nick Rolando

Reputation: 26177

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";

Upvotes: 1

Vincent Ramdhanie
Vincent Ramdhanie

Reputation: 103145

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

Upvotes: 1

Related Questions