CODEWITHSUNDEEP
mysqldatabasestructure
user316602
user316602

Reputation:

How to structure a list in MySQL?

I'm creating a website and part of the functionality is that a user can create a list. Each user can only have one list, and each list only has one user. Each list also has a bunch of items. Items can be used in multiple lists.

So let's say I have the following tables:

How can I define which items a list has? Right now I'm storing a string of CSV's in the Lists table, but I doubt that's the best way.

Thanks!

Upvotes: 1

Views: 178

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

WIth your restriction that a user can have only one list (or none) and each list belonging to exactly one user, I would do:

User
  userid
  username
  Primary Key (userid)

List
  listid
  listname
  Primary Key (listid) 
  Foreign Key (listid) References (User.userid)

ListItem
  listid
  itemid 
  Primary Key (listid, itemid) 
  Foreign Key (listid) References (List.listid)
  Foreign Key (itemid) References (Item.itemid)

Item
  itemid
  description 
  Primary Key (itemid)

Upvotes: 1

JohnFx
JohnFx

Reputation: 34917

I'd structure it like this

Users
UserID (Primary Key)
UserName
ListID (foreign key to Lists.ListID)

Lists
ListID (Primary Key)
ListName

ListsToListItems
ListID (Foreign Key to Lists.ListID)
ListItemID (Foreign Key to ListItems.ListItemID)

ListItems
ListItemID (Primary Key)
ListItemValue

Upvotes: 2

Rob Raisch
Rob Raisch

Reputation: 17377

Typically you would have table definitions like:

create table user (
  id int,
  name varchar,
  list_id int, /* optional */
  etc.
)

create table list (
  id int,
  name varchar,
  owner_id int
  etc.
)

create table item (
  id int,
  name varchar,
  etc.
)

create table item_x_list (
  id int,
  list_id int,
  item_id int
)

such that:

user -> list -> item_x_list -> item*

Note that you could skip the list table altogether if you didn't need list-specific data like a name.

Upvotes: 3

Jacob Eggers
Jacob Eggers

Reputation: 9332

You'll need another table Lists_Items with two columns:

list_id
item_id

Upvotes: 2

Related Questions