How to store user-created lists with SQLite?

Question

I'm trying to make a simple app where Users can make Lists of films/books they'd like to complete. Once a List is created, they could add to a List, or reorder the items in the List.

So currently I have a User table:

CREATE TABLE User (
    userid   INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    username TEXT    NOT NULL UNIQUE,
    password TEXT    NOT NULL,
    salt     TEXT    NOT NULL UNIQUE
);

And a List table:

CREATE TABLE List (
    listid  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    listname TEXT NOT NULL,
    userid INTEGER NOT NULL,
    date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    date_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    FOREIGN KEY(userid) REFERENCES User(userid)
);

What I'm trying to figure out now is: how do I store the actual lists? The lists are user-created, and a user should have the ability to add and remove from them, and re-order the items of the list if they'd like. I'd also like to store some metadata with each list item (such as a url to a film's respective Wikipedia page).

At first I thought, I'll just store the list as JSON in a column in the List table. But this seems counter-intuitive in SQL.

A quick cursory search-spree led me to people talking about junction tables. I'm not sure I fully understand junction tables yet; but does this mean that each time a user would create a new List, I'll have to generate a new table for all of the items of the List? (So, as I create a new row in the List table, I'll also create a new table ListItems_ListID_Username that links to that row?).

Any insight appreciated. If it's not obvious, I'm a total SQL newbie. :)

EDIT: As an example, if I were to store each list item in a table, I imagine each list item would look like this psuedo schema

(orderInList INTEGER, itemname TEXT, url TEXT (nullable), listid INTEGER (foreign key to List), userid INTEGER (foreignkey to User))

First note, **do not ever** store passwords as plain text. What the list contains? eg: ListId, ListName, CreationDate, LastUpdate, Films? — Ilyes, Oct 11 '20 at 19:00
@Ilyes Thank you. Yes, the passwords are hashed with `pbkdf2` w/ a unique salt for each user before they are stored in the database. I hope this is OK. :) As an example, a list item can potentially look like this: (itemname, url (nullable), listid (foreign key), userid (foreignkey)). I'll update my question with this example. — keb, Oct 11 '20 at 19:04
I think you can create another table called list_details with all details for one list. so for an user, for a list, there can be multiple items/stuff. When retrive the data, you can `join` these three tables i.e. user,list,list_details to fetch data for an user. Because user can create one list for movies and other for books. so list details will effectively store them with 2 separate list. `JSON` idea is fine if your DB is small. — Koushik Roy, Oct 11 '20 at 19:15
This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. See [ask] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. PS Follow a published academic textbook on information modelling, the relational model & DB design & querying. (Manuals for languages & tools & wiki articles or web posts are not such textbooks.) — philipxy, Oct 12 '20 at 09:44

sticky bit · Accepted Answer · 2020-10-12T09:38:49.757

does this mean that each time a user would create a new List, I'll have to generate a new table for all of the items of the List?

No! That's the worst thing you can do.

Items are one entity, therefore there is only one table for them. The junction table then links the items to the lists.

In your case that could look like:

CREATE TABLE list
             (listid integer
                     NOT NULL
                     AUTOINCREMENT,
              userid integer
                     NOT NULL,
              ...
              PRIMARY KEY (listid),
              FOREIGN KEY (userid)
                          REFERENCES user
                                     (userid));

CREATE TABLE item
             (itemid integer
                     NOT NULL
                     AUTOINCREMENT,
              ...
              PRIMARY KEY (itemid));

CREATE TABLE listitem
             (listid integer
                     NOT NULL,
              itemid integer
                     NOT NULL,
              ...
              PRIMARY KEY (listid,
                           itemid),
              FOREIGN KEY (listid)
                          REFERENCES list
                                     (listid),
              FOREIGN KEY (itemid)
                          REFERENCES item
                                     (itemid));

So if a user with ID 1 had a list with ID 1 that contains an item with ID 1 you'd have a record

.________.________._____.
| listid | userid | ... |
+--------+--------+-----+
| 1      | 1      | ... |
'--------'--------'-----'

in list, a record

.________._____.
| itemid | ... |
+--------+-----+
| 1      | ... |
'--------'-----'

in item and a record

.________.________._____.
| listid | itemid | ... |
+--------+--------+-----+
| 1      | 1      | ... |
'--------'--------'-----'

in listitem.

But(!) there's one thing to keep in mind here and why you possibly don't want to use that approach. A user with ID 2 could also have this item on their list with ID 2. That would then look like:

.________.________._____.
| listid | userid | ... |
+--------+--------+-----+
| 1      | 1      | ... |
+--------+--------+-----+
| 2      | 2      | ... |
'--------'--------'-----'

.________._____.
| itemid | ... |
+--------+-----+
| 1      | ... |
'--------'-----'

.________.________._____.
| listid | itemid | ... |
+--------+--------+-----+
| 1      | 1      | ... |
+--------+--------+-----+
| 2      | 1      | ... |
'--------'--------'-----'

As you can see there is only one item. If any of the users changes something to the item the other user is affected by that change too. Now in a lot of circumstances this exactly what one wants. But in your case you possibly want that an item is unique to a list not globally. Even if the book/movie is the same, each user may want to have their own record of it, with their own notes, etc. So you probably don't want a junction table but just a foreign key in the item table pointing to the list.

CREATE TABLE list
             (listid integer
                     NOT NULL
                     AUTOINCREMENT,
              userid integer
                     NOT NULL,
              ...
              PRIMARY KEY (listid),
              FOREIGN KEY (userid)
                          REFERENCES user
                                     (userid));

CREATE TABLE item
             (itemid integer
                     NOT NULL
                     AUTOINCREMENT,
              listid integer
                     NOT NULL,
              ...
              PRIMARY KEY (itemid),
              FOREIGN KEY (listid)
                          REFERENCES list
                                     (listid);

-- no table listitem

Now an item belongs to exactly one list and any change done to the item will only affect the one item in that list.

Of course you could also have the items globally unique but not changeable by the users. To give the users the chance to have their own data for an item on their list you could add columns in listitem where this information could be stored.

score 0 · Answer 2 · answered Oct 11 '20 at 19:22

You can create a third table to store the items of each list owned/created by a specific user as the following:

CREATE TABLE Users(
  UserId INTEGER PK,
  UserName TEXT(n)
  .
  .
);

CREATE TABLE Lists(
  ListId INTEGER PK,
  ListName TEXT(n) NOT NULL,
  ListOwner INTEGER NOT NULL FK (UserId),
  CreationDate DATETIME NOT NULL,
  LastUpdate DATETIME NULL
);

CREATE TABLE ListItems(
  Id INTEGER PK,
  ListId INETGER FK,
  ItemName TEXT(n),
  ItemURL TEXT(n)
);

How to store user-created lists with SQLite?

2 Answers2