Как хранить списки, созданные пользователями, с помощью SQLite?

Я пытаюсь сделать простое приложение, в котором пользователи могут составлять списки фильмов/книг, которые они хотели бы закончить. После создания списка они могут добавлять в список или изменять порядок элементов в списке.

Итак, в настоящее время у меня есть таблица пользователей:

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

И таблица списка:

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

Сейчас я пытаюсь понять: как мне хранить фактические списки? Списки создаются пользователями, и у пользователя должна быть возможность добавлять и удалять из них, а также изменять порядок элементов списка, если они хотят. Я также хотел бы хранить некоторые метаданные с каждым элементом списка (например, URL-адрес соответствующей страницы Википедии фильма).

Сначала я подумал, что просто сохраню список в формате JSON в столбце таблицы List. Но это кажется нелогичным в SQL.

Быстрый беглый поиск привел меня к людям, говорящим о соединительных таблицах. Я еще не уверен, что полностью понимаю соединительные таблицы; но означает ли это, что каждый раз, когда пользователь будет создавать новый список, мне придется создавать новую таблицу для всех элементов списка? (Итак, поскольку я создаю новую строку в таблице List, я также создам новую таблицу ListItems_ListID_Username, которая ссылается на эту строку?).

Любое понимание ценится. Если это не очевидно, я полный новичок в SQL. :)

РЕДАКТИРОВАТЬ: В качестве примера, если бы я должен был хранить каждый элемент списка в таблице, я думаю, что каждый элемент списка будет выглядеть как эта псевдосхема

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

sql database sqlite database-design junction-table
person keb    schedule 11.10.2020    source источник
comment
Во-первых, никогда не храните пароли в виде обычного текста. Что содержит список? например: ListId, ListName, CreationDate, LastUpdate, Films?   -  person Ilyes    schedule 11.10.2020
comment
@Ilyes Спасибо. Да, пароли хэшируются с помощью pbkdf2 с уникальной солью для каждого пользователя, прежде чем они будут сохранены в базе данных. Я надеюсь, что это нормально. :) Например, элемент списка потенциально может выглядеть так: (имя элемента, URL-адрес (обнуляемый), listid (внешний ключ), идентификатор пользователя (внешний ключ)). Я обновлю свой вопрос этим примером.   -  person keb    schedule 11.10.2020
comment
Я думаю, вы можете создать еще одну таблицу с именем list_details со всеми деталями для одного списка. поэтому для пользователя для списка может быть несколько элементов/вещей. При извлечении данных вы можете join эти три таблицы, т. е. user, list, list_details, чтобы получить данные для пользователя. Потому что пользователь может создать один список для фильмов и другой для книг. поэтому детали списка будут эффективно хранить их в 2 отдельных списках. JSON идея хороша, если ваша БД маленькая.   -  person Koushik Roy    schedule 11.10.2020
comment
Это часто задаваемые вопросы. Пожалуйста, прежде чем рассматривать публикацию, прочитайте свой учебник и / или руководство и погуглите любое сообщение об ошибке или множество четких, кратких и точных формулировок вашего вопроса / проблемы / цели, с вашими конкретными строками / именами и тегами site: stackoverflow.com и без них; прочитал много ответов. Отразите свое исследование. См. раздел Как задать вопрос и тексты при наведении указателя мыши на стрелку голосования. Если вы публикуете вопрос, используйте одну фразу в качестве заголовка. PS Следуйте опубликованному академическому учебнику по информационному моделированию, реляционной модели, проектированию БД и запросам. (Руководства по языкам и инструментам, вики-статьи или веб-публикации не являются такими учебниками.)   -  person philipxy    schedule 12.10.2020

Ответы (2)


arrow_upward
2
arrow_downward

означает ли это, что каждый раз, когда пользователь будет создавать новый список, мне придется создавать новую таблицу для всех элементов списка?

Нет! Это худшее, что вы можете сделать.

Элементы представляют собой одну сущность, поэтому для них существует только одна таблица. Затем соединительная таблица связывает элементы со списками.

В вашем случае это может выглядеть так:

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

Таким образом, если бы у пользователя с идентификатором 1 был список с идентификатором 1, содержащий элемент с идентификатором 1, у вас была бы запись

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

в list запись

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

в item и запись

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

in listitem.

Но (!) здесь нужно помнить одну вещь и почему вы, возможно, не хотите использовать этот подход. Пользователь с идентификатором 2 также может иметь этот элемент в своем списке с идентификатором 2. Тогда это будет выглядеть так:

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

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

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

Как видите, есть только один элемент. Если кто-либо из пользователей что-то изменит в элементе, это изменение затронет и другого пользователя. Во многих случаях это именно то, что нужно. Но в вашем случае вы, возможно, хотите, чтобы элемент был уникальным для списка, а не глобально. Даже если книга/фильм одинаковы, каждый пользователь может захотеть иметь свою собственную запись о ней со своими собственными примечаниями и т. д. Так что вам, вероятно, не нужна соединительная таблица, а просто внешний ключ в таблице элементов, указывающий на список.

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

Теперь элемент принадлежит ровно одному списку, и любое изменение, внесенное в элемент, повлияет только на один элемент в этом списке.

Конечно, вы также можете иметь элементы, глобально уникальные, но не изменяемые пользователями. Чтобы дать пользователям возможность иметь свои собственные данные для элемента в своем списке, вы можете добавить столбцы в listitem, где эта информация может храниться.

person sticky bit    schedule 11.10.2020

arrow_upward
0
arrow_downward

Вы можете создать третью таблицу для хранения элементов каждого списка, принадлежащего/созданного определенным пользователем, следующим образом:

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)
);
person Ilyes    schedule 11.10.2020