Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Проектирование БД Новый топик    Ответить
 Не могу придумать структуру БД  [new]
stoi
Member

Откуда:
Сообщений: 8
Задача примерно такая:

Придумать структуру для каталогизатора допустим фотографий.
Есть простая таблица со ссылками на файлы фотографий.
Пользователь может добавлять неограниченное количество собственных фильтров (тегов).
У каждого из фильтров может быть задано произвольное число предопределенных значений.
К примеру:
Фильтр "Место съемки". Значения "Москва", "Тамбов", "Мухосранск"
Фильтр "Время съемки". Значения "Утром", "Днем", "Вечером"

ВАЖНО! Для одной фотографии не может быть задано более одного значения одного фильтра.
Т.е. у Фото1 не может соответствовать два разных значения фильтра(тега) "Место съмки" - "Москва" и "Мухосранск".

Вроде задача несложная. Но оптимальную структуру БД придумать не могу.
Допустим так:

CREATE TABLE [cards](
    [front] TEXT NOT NULL, 
    [back] TEXT NOT NULL);

CREATE TABLE [cardsfiltresvalues](
    [card] TEXT NOT NULL REFERENCES cards, 
    [filtervalue] TEXT NOT NULL REFERENCES filtresvalues, 
    [filter] TEXT NOT NULL REFERENCES filtres, 
    UNIQUE([card], [filter]));

CREATE TABLE [filtres](
    [name] TEXT NOT NULL);

CREATE TABLE [filtresvalues](
    [filter] TEXT NOT NULL REFERENCES filtres, 
    [value] TEXT NOT NULL);


Здесь у меня не фотографии, а некие "cards". Но роли не играет.
Линковочная таблица cardsfiltresvalues мне не нравится. По идее, колонка "filter" не нужна. Ибо у значения колонки "filtervalue" уже есть родительский элемент и его можно получить. Но если её убрать - как добиться уникальности UNIQUE([card], [filter])?
А с ней - нет гарантии что родительский элемент значения "filtervalue" будет совпадать с значением колонки "filter". А должен обязательно...

Что я делаю не так? Тип СУБД не очень важен. Мне важно понять - как решается такая задача. Ведь не я ж первый её решаю, явно.
Спасибо за помощь!
22 авг 17, 13:08    [20741175]     Ответить | Цитировать Сообщить модератору
 Re: Не могу придумать структуру БД  [new]
982183
Member

Откуда:
Сообщений: 1358
stoi
ВАЖНО! Для одной фотографии не может быть задано более одного значения одного фильтра.


На практике - не факт
Тема: животные/юмор
Участники: Родственники/коллеги
Вполне может существовать несколько значений в одном теге.
22 авг 17, 13:11    [20741185]     Ответить | Цитировать Сообщить модератору
 Re: Не могу придумать структуру БД  [new]
stoi
Member

Откуда:
Сообщений: 8
автор
Вполне может существовать несколько значений в одном теге.

Да. Но задача взята для примера. Там не фотографии в реальности. И нужно именно ОДНО значение.
Вот это всё и портит ))
Ну или я туплю...
22 авг 17, 13:14    [20741201]     Ответить | Цитировать Сообщить модератору
 Re: Не могу придумать структуру БД  [new]
982183
Member

Откуда:
Сообщений: 1358
Да нет, наоборот облегчает.

Непонятен подход к проектирования структуры БД.
Это явно не делается через скрипты её создания.
+ Отсутствуют ID поля.
22 авг 17, 14:18    [20741418]     Ответить | Цитировать Сообщить модератору
 Re: Не могу придумать структуру БД  [new]
stoi
Member

Откуда:
Сообщений: 8
982183, Да бог с ним с подходом ) Дался он вам!
Признаюсь, я не дока в проектировании БД. Просто набросал в SQLite Expert БД, а сюда выложил ddl.
Еще на бумажке диаграммы рисовал. Но их выложить сложнее ).

У вас по сути вопроса есть какие-нибудь предложения?
22 авг 17, 14:26    [20741444]     Ответить | Цитировать Сообщить модератору
 Re: Не могу придумать структуру БД  [new]
982183
Member

Откуда:
Сообщений: 1358
Много буков
почистил, добавил ID, осталось добавить комменты, а потом можно думать

[cards].[id]
[cards].[front]
[cards].[back]

[cardsfiltresvalues]. [card_id]
[cardsfiltresvalues]. [filtervalue]
[cardsfiltresvalues]. [filter]

[filtres].[id]
[filtres].[name]

[filtresvalues] .[ filtres id]
[filtresvalues] .[cardsfiltresvalues_id]
[filtresvalues]. [value]

Не вижу справочника "тегов"
22 авг 17, 14:28    [20741452]     Ответить | Цитировать Сообщить модератору
 Re: Не могу придумать структуру БД  [new]
stoi
Member

Откуда:
Сообщений: 8
982183, Применительно к фотографиям, будет так:

[photos].[id]
[photos].[path]

[photostagsvalues]. [photo_id]
[photostagsvalues]. [tags_id]
[photostagsvalues]. [tagsvalues_id]

[tags].[id]
[tags].[name]

[tagsvalues] .[id]
[tagsvalues] .[tags_id]
[tagsvalues]. [value]
22 авг 17, 14:49    [20741532]     Ответить | Цитировать Сообщить модератору
 Re: Не могу придумать структуру БД  [new]
stoi
Member

Откуда:
Сообщений: 8
А еще лучше так (изменил порядок таблиц):

[photos].[id]
[photos].[path]

[tags].[id]
[tags].[name]

[tagsvalues] .[id]
[tagsvalues] .[tags_id]
[tagsvalues]. [value]


[photostagsvalues]. [photo_id]
[photostagsvalues]. [tags_id]
[photostagsvalues]. [tagsvalues_id]

Ключевая таблица - последняя. Она связывает фотографии со значениями тэгов.
Можно удалить лишнее поле tags_id и получится:
[photostagsvalues]. [photo_id]
[photostagsvalues]. [tagsvalues_id]

И всё б хорошо, но тогда не получится обеспечить уникальность пар photo_id <-> tags_id
22 авг 17, 14:54    [20741547]     Ответить | Цитировать Сообщить модератору
 Re: Не могу придумать структуру БД  [new]
982183
Member

Откуда:
Сообщений: 1358
А ты хочешь это сделать на уровне БД?
22 авг 17, 15:10    [20741594]     Ответить | Цитировать Сообщить модератору
 Re: Не могу придумать структуру БД  [new]
982183
Member

Откуда:
Сообщений: 1358
Хранить по второму варианту может быть и правильнее,
Но всё равно при вводе сначала надо выбрать тэг, потом его значение.
22 авг 17, 15:14    [20741610]     Ответить | Цитировать Сообщить модератору
 Re: Не могу придумать структуру БД  [new]
stoi
Member

Откуда:
Сообщений: 8
982183,

Я не знаю - можно ли сделать это на уровне БД. Потому и спрашиваю у профессионалов )
Любопытно - решаемо ли это на уровне БД?
автор
Хранить по второму варианту может быть и правильнее,
Но всё равно при вводе сначала надо выбрать тэг, потом его значение.


Тогда уникальность нужно контролировать при вводе данных. Каждый раз придется делать выборку. Неоптимально.
В первом варианте есть лишнее поле. Но зато при вводе ничего проверять не нужно. ИМХО - это более правильно.
Тем более - БД будет не сильно большая, одно лишнее поле - не утянет

Модератор: Тема перенесена из форума "SQLite".
22 авг 17, 16:13    [20741837]     Ответить | Цитировать Сообщить модератору
 Re: Не могу придумать структуру БД  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 7688
У Вас что-то непонятное с ключами - например, что является ключом в cards?
22 авг 17, 18:04    [20742182]     Ответить | Цитировать Сообщить модератору
 Re: Не могу придумать структуру БД  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 7688
Посмотрел на более позднюю структуру 20741547
Почему бы Вам в tagvalues не сделать ключом (tag_id, value) и соответственно ссылаться на этот ключ из photostagsvalues? Таким образом ссылочная целостность не даст ввести в photostagsvalues значение tag_id, не соответствующее значению фильтра.
22 авг 17, 18:30    [20742255]     Ответить | Цитировать Сообщить модератору
 Re: Не могу придумать структуру БД  [new]
Pu4koff
Member

Откуда:
Сообщений: 270
stoi, значения фильтров могут только браться из общего справочника или пользователь может ввести произвольный?
Скажем, в базе хранится, что Место съемки может быть "Москва", "Тамбов", "Мухосранск". Пользователь хочет ввести "Пенза". Разрешаем ему это и записываем в базу или посылаем куда подальше? Если разрешаем и записываем Пензу, то для следующей фотографии мы на выбор даём "Москва", "Тамбов", "Мухосранск", "Пенза" или только то, что было изначально? т.е. Пенза запишется к конкретной фотографии или нужно её положить в общую таблицу?
22 авг 17, 20:23    [20742431]     Ответить | Цитировать Сообщить модератору
 Re: Не могу придумать структуру БД  [new]
982183
Member

Откуда:
Сообщений: 1358
А почему бы нет?
Но это не вопрос к структуре БД.
23 авг 17, 02:35    [20742723]     Ответить | Цитировать Сообщить модератору
 Re: Не могу придумать структуру БД  [new]
stoi
Member

Откуда:
Сообщений: 8
Друг прислал диаграмму (в прикреплении). Говорит - нашел решение. Но из диаграммы непонятно - как создаются ключи (

К сообщению приложен файл. Размер - 12Kb
23 авг 17, 10:19    [20743126]     Ответить | Цитировать Сообщить модератору
 Re: Не могу придумать структуру БД  [new]
stoi
Member

Откуда:
Сообщений: 8
Сам себе отвечу. Друг решил задачку. Респект ему. Я пошел читать про внешние составные ключи...

CREATE TABLE [photos] (
	[photo_id]	integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	[photo_value]	nvarchar(100) NOT NULL COLLATE NOCASE

);

CREATE TABLE [photos_tags_values](
    [photo_id] integer NOT NULL REFERENCES photos([photo_id]) ON DELETE NO ACTION ON UPDATE CASCADE, 
    [tag_id] integer NOT NULL, 
    [tag_value_id] integer NOT NULL, 
    PRIMARY KEY([tag_id], [tag_value_id]), 
    FOREIGN KEY([tag_id], [tag_value_id]) REFERENCES tag_values([tag_id], [tag_value_id]) ON DELETE NO ACTION ON UPDATE CASCADE);

CREATE TABLE [tags] (
	[tag_id]	integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	[tag_value]	varchar(100) NOT NULL COLLATE NOCASE

);

CREATE TABLE [tag_values](
    [tag_value_id] integer NOT NULL, 
    [tag_id] integer NOT NULL REFERENCES tags([tag_id]) ON DELETE NO ACTION ON UPDATE CASCADE, 
    [value] varchar(30) NOT NULL COLLATE NOCASE, 
    PRIMARY KEY([tag_id], [tag_value_id]));

CREATE UNIQUE INDEX [photos_tags_values_photos_tags_values_uq]
ON [photos_tags_values](
    [photo_id] DESC, 
    [tag_id] DESC);

CREATE UNIQUE INDEX [tag_values_tag_values_uq]
ON [tag_values](
    [tag_id] DESC, 
    [value] DESC);

CREATE UNIQUE INDEX [tag_values_UQ__tag_valu__AD4EDF21FEECC733]
ON [tag_values](
    [tag_value_id] DESC);
23 авг 17, 11:21    [20743422]     Ответить | Цитировать Сообщить модератору
 Re: Не могу придумать структуру БД  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 7688
stoi
Сам себе отвечу. Друг решил задачку. Респект ему. Я пошел читать про внешние составные ключи...

20742255
23 авг 17, 11:53    [20743543]     Ответить | Цитировать Сообщить модератору
Все форумы / Проектирование БД Ответить