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))

Answer 1

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.

Answer 2

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