Помощь с архитектурой базы данных

учи теорию реляционных баз данных, на ютюбе видео курсы должны быть по базам данных. там (в теории рбд) это НФ (нормальные формы) называется.

Вроде оно, спасибо!

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

Скажем, есть альбомы, которые пишут несколько исполнителей совместно. Есть случаи, когда люди были в нескольких группах-исполнителях. И так далее. Как в такой ситуации правильно создать связи на примере альбомов?

В конечном итоге все связи выраждаются во многие-ко-многим. Делается через доп-таблицу.

Реально, тебе надо что-то по теории бд почитать.

Один к одному: обе таблицы ссылаются друг на друга. Ссылки делают по primary key — просто кладут id.

Один ко многим: ссылается только одна таблица, а вторая не ссылается.

Многие ко многим: делается отдельная таблица, каждая запись содержит два id. Нужно запрашивать поиск по ней, чтобы получить все связи.

Также нужно учесть удаление записей. Может случится так, что по сохранённому id уже ничего нет. Существуют разные стратегии на этот счёт:

• Ничего не делать. Тогда везде нужны проверки, чтобы не упасть с исключением.
• Сделать поле null, когда запись удаляется — занулять его. Тоже понадобятся проверки, но уже на null, что гораздо эффективнее и занимает меньше места в коде.
• Удалить все ссылающиеся записи.

Для примера можно посмотреть на любую современную ORM, там это делают на уровне фреймворка, включая обработку связей в случае удаления записи. В Django весьма удобная ORM.

Предположу, что следующим вопросом будут миграции. Вот на них тоже лучше посмотреть в Django. Вручную они делаются легко:

• Заводим таблицу, где строковой ключ — имя другой, целевой таблицы, а второе поле integer — её версия.
• В коде пишем проверку, если версия не совпадает — применяем соответствующую миграцию.

Это можно сделать даже на уровне SQL в навороченных СУБД, таких как Postgres.

Лучше сразу использовать для этого БД - контролировать корректность данных и прочие целостности должна она. Никакие ОРМ и фреймворки не имеют отношения к данным - они лишь потребители. Только (её величество) БД знает где какие данные и их распределения существуют и в какой момент жизни приложения их контролировать. В некоторых вселенных и html/JSON-ответ сама СУБД отдает, больше ничего не нужно.

Немного экспрессивно-маргинально, но чтобы обозначить разнообразие :)

тебе нужно научиться в SQL? открой любой туториал и учись. начинать САМО СОБОЙ надо не с создания БД, а с запросов к уже существующей.

удачи.

А еще можно использовать внешние ключи и тогда сама БД за тебя лишнее поудаляет.

ТСу вот это всё (орм дрм и тдм) только и нужно…

Зачем так много таблиц? Нужно завести одну таблицу, назову ее t_entity

ID INTEGER AUTO_INCREMENT,
ENTITY TEXT,
NAME TEXT,
LINK_SRC INTEGER,
LINK_DST INTEGER

Теперь можно создавать группы и альбомы, создаешь исполнителя AC/DC, просто вставляя строку с такими значениями столбцов (неуказанные пусть будут NULL)

ID=1
ENTITY=artist
NAME=AC/DC

Создаешь альбом Highway to Hell

ID=2
ENTITY=album
NAME=Highway to Hell

Даешь альбому авторство AC/DC

ID=3
ENTITY=link_author
NAME=Исполнитель
LINK_SRC=2
LINK_DST=1

А теперь можно теперь создать не только дополнительных авторов, но и студию

ID=4
ENTITY=studio
NAME=Roundhouse

И привязать ее к альбому

ID=5
ENTITY=link_author
NAME=Студия
LINK_SRC=2
LINK_DST=4

И даже не пришлось менять структуру таблицы, а ведь такое ты явно заранее не продумал, да?

Выбрать все альбомы группы AC/DC можно так

SELECT t_entity.* FROM t_entity
INNER JOIN t_entity t_entity_link_author 
  ON t_entity.ENTITY = "album"
  AND t_entity_link_author.ENTITY = "link_author"
  AND t_entity.ID = t_entity_link_author.LINK_SRC
  AND t_entity_link_author.LINK_DST=1;

Исполнитель - это группа чтоли? Название неудачное.

А разве не могут над альбомом работать как группы, так и отдельные сольные участники?

Нужно завести одну таблицу, назову ее t_entity

Это придумывание своего SQL поверх существующего. Многие нубы начинают с этого.

Нужно завести одну таблицу

ну, меньше то и смысла нет (с)

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

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

Ну дык, я ж не спорю! Нормальная идея, можно её развить и углубить. Все файлы можно хранить в одной директории. Если им дать осмысленные имена, то можно грепать ls по суффиксу и не засорять файловую систему всякими вложенными структурами. Польза очевидна!

Не, лучше каждый класс в отдельный файл по отдельному неймспейсу в своей директории. А еще лучше каждый класс в свой npm репозиторий. Понял к чему ты клонишь, но смузи мне хлебать не нравится.

Дружище, нормальные формы придумали еще до твоего рождения. И даже Entity-Attribute-Value паттерн, который ты пытаешься переизобрести придумали до твоего рождения. Не получится у тебя выступить с инновационными идеями на таком сильно истоптанном поле как РДБМС, узбагойся.

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

никогда нельзя начинать разработку приложения с модели БД - на выходе получится откровенный шлак, который или придется постоянно переписывать в попытках получить что-то вменяемое или поддерживать какими-то невероятными усилиями - оставьте ER-диаграммы 70-летним дидам для забавы.

В первую очередь нужно понять какие сущности в приложении вообще бывают и какие операции над ними могут производиться (тут придется погрузиться в предметную область и пораскинуть мозгами, например группам свойственно менять состав, вот смена состава - это что? новая группа, новая версия группы или просто записи в истории? Альбомам свойственно переиздаваться - получаем новый альбом или нет? У песен бывают разные исполнения, каверы и пр.), во вторую очередь нужно определить что в итоге будет показываться конечному пользователю, а таблички в СУБД любой ORM уже давно сам рисовать имеет.

Зависит от СУБД. Не все так умеют, вот у него sqlite, в ней минимум фич. Когда возможно, ORM сами используют правильные привязки.

Но для опыта конечно полезно прописать их руками в сыром SQL.

В некоторых задачах это верных подход. Например, когда это какой-нибудь лог. Главное енумы правильно организовать. В django, например, есть удобные choices.

Если писать ручками — опять же, помнить о возможностях СУБД. В sqlite так, но в postgres иначе, потому в нём имеются нативные енумы.

Разработку начинают с планирования архитектуры в uml. Но у него тут маленькая учебная программка, для неё такой подход был бы лишним усложнением. Сначала надо SQL выучить, базовые паттерны проектирования хотя бы по названиям запомнить, а потом уже uml рисовать. Ничего толкового без менторства новичок всё равно не нарисует.

так и делают в полноценных СУБД, где такие фичи есть

А вот если кроме названий ещё что-то нужно будет хранить? :)
Например:

• у артиста - дату рождения,
• у альбома - картинку обложки
• у студии - адрес размещения.

Не будет ли удобнее в разных таблицах хранить?

Не будет ли удобнее в разных таблицах хранить?

безусловно, Морковкин ерунде вас учит

Это одна из реализаций спискоты/иерархии поверх реляционщины (таблицы).

Нахрена это ТСу мы вряд ли узнаем, ибо упоринум.

Это не финальная таблица высеченная в камне, минимальный пример, нужны поля - нужно добавить. Можно что то и в отдельную таблицу вынести, если будет необходимость.

Вот PICTURE следует добавить, он почти у всего есть, у студии, у обложки, у группы, у исполнителя. Номер следует добавить, потому что несколько студий, несколько альбом в определенном порядке, треки в определенном порядке.

Основные поля лучше хранить в такой таблице, которые часто используются. Для редких можно сделать вторую, либо по такой же схеме, либо по схеме ID|ENTITY_ID|NAME|VALUE_INTEGER|VALUE_STRING|VALUE_DATE или совмещено, потому что может возникнуть желание к определенным трекам добавить какую то информацию которая будет только для этой группы актуальна.

А ваша фиксированная схема это фашизм, угнетение человеческой воли. Бытие определяет сознание.

Нужно завести одну таблицу, назову ее t_entity

Triple store изобретаешь? %) Дело хорошее, конечно. Но если что, есть и готовые, обкатанные реализации — типа Datomic.

А какие можно выделить преимущества у схемы с 1 таблицей, по сравнению со схемой с 3 таблицами (отдельными для артиста/альбома/студии)?
Я вот пока вижу только недостатки:

1. чтобы выбрать информацию только по артистам/альбомам/студиям придётся к запросу добавлять фильтр WHERE ENTITY=…
2. каждый запрос будет дольше исполняться из-за того что количество записей в 1 общей таблице больше, чем кол-во записей в отдельных таблицах (а при увеличении количества записей до миллионов - страшно подумать что будет :) )

Но наверно должны быть и какие-нибудь достоинства?

Динамика vs Статика. Ситуацию с добавление студии к альбому я уже описал.

Фактически кей-велью. Хм.. а как там будет реализован контроль целостности данных? Например, чтобы нельзя было добавить альбом, ссылающийся на несуществующего исполнителя? Или удалить исполнителя, написавшего что-то (если только через delete cascade). Ну, так, чтоб прям железно.

Или удалить исполнителя, написавшего что-то

SET REMOVED = «Y»

Например, чтобы нельзя было добавить альбом, ссылающийся на несуществующего исполнителя?

Если ты в коде выбираешь неправильные ID для добавления, то скорее всего это будут неправильные исполнители, а не несуществующие ID, поэтому к моей схеме это не относится, можешь вести историю и отменять изменения за определенный промежуток. LINK_SRC всегда указывает на ID поэтому точно такое же ограничение можно поставить как и при обычной схеме.

Разработку начинают с планирования архитектуры в uml

нужно было еще вспомнить какие-нить case-средства, типа ERWin или BPWin, вот нет, современная разработка сейчас скорее заканчивается UML, нежели начинается с него, когда какой-нить дед запросит красивые картинки, разобраться в которых он все равно не сможет, но ЧСВ все равно требует что-то запросить. Посмотрите на ютубе всякие моки технических интервью - никакого UML там и близко нет.

Сначала надо SQL выучить

SQL учить не нужно, вот серьезно, за последние лет 10 именно для разработки я ничего сложнее outer join не использовал, ибо просто не нужно - если БД не косая, то никакой эквилибристики с ней устраивать не нужно, да и концепции двадцатилетней давности в духе «круто выгружать данные одним запросом» не выдерживают совершенно никакой критики - просто нужно писать простые и понятные модели, а если начать учить SQL, то там всяко плохому научат в духе натуральных, или еще хуже композитных, ключей, да еще и FK на них, уровней изоляции выше RC, нормальных форм и прочей белиберды, которая есть в стандарте, но на практике совершенно не работает (в качестве примера: посмотрите на паттерн double-entry bookkeeping - он нарушает 3NF, однако, используется он повсеместно), если в новичка вгружать академические знания, то на выходе получим не разработчика, а учителя информатики.

современная разработка сейчас скорее заканчивается UML, нежели начинается с него

Как минимум mind map делается на старте. Таков мой субъективный опыт: я сначала думаю, потом делаю. Знаю тех, кто поступает так же, это не то что бы редкость. От задачи зависит.

а последние лет 10 именно для разработки я ничего сложнее outer join не использовал

Паршиво для развития. Мне, наоборот, приходится оптимизировать запросы, потому что база — узкое место. И ручками их править тоже приходилось много раз.

Как минимум mind map делается на старте.

так mind map или UML?

Паршиво для развития

ну не знаю… в pure SQL общеизвестных трюков где-то десятка полтора-два (тут куда важнее усидчивость: как и ждать сутками когда индексы создадутся, так и чужое г. перелопачивать - фана здесь нет никакого совершенно), использование outer join вместо inner - один из таких трюков :)

Не, а как запретить удаление исполнителя? (при наличии у него альбомов/песен).

Неправильные, но существующие ID - это бизнес-плоскость, а как запретить вносить именно несуществующие?

И если пытаться этот контроль реализовывать каким-то процессом, отличным от процесса, читающего файлы БД, будут проблемы с транзакционностью, защитой и производительностью.

Не, а как запретить удаление исполнителя?

ну неужели не видно, что Морковь стебется? У любой СУБД от селф-жойнов сносит башню и схемарь попросту неработоспособный, следующим продолжением стеба будет создание единственной таблицы (id, type, json)

начинать САМО СОБОЙ надо не с создания БД, а с запросов к уже существующей.

Я редко соглашаюсь с этим регистрантом, но в данном случае поддержу. Правда с уточнением, что образцы баз надо брать хорошие. А то я даже в продакшене видел БД, в которых с нормализацией всё совсем плохо, и не потому, что это что-то оптимизирует (ИНОГДА денормализация действительно оправдана), а потому, что «я же так могу».

ИНОГДА денормализация действительно оправдана

quick fix: ВСЕГДА оправдана

Да подумал, что я неточно выразился про удалять / не удалять.

А конструкторы, предлагающие «давайте всё хранить в одной табличке - будет супергибко», потом сталкиваются с отсутствием в их БД функций раскрутки дерева, например :)

следующим продолжением стеба будет создание единственной таблицы (id, type, json)

Ну для тебя это стёб, а я помню техпроект, автор которого предлагал все данные укладывать в 4 таблицы и выдавал это за киллерфичу. Более того, у него одна из таблиц и называлась «Entity», кажется. :))) На практике это означает перенос сложности со стороны сервера на сторону клиента и отказ от контроля целостности, для которого, в частности, реляционные СУБД и нужны.

К счастью, на одном проекте всё и заглохло. Надеюсь, ТС такие практики перенимать не будет.

Можно ещё вспомнить метаданные 1С, но если я не ошибаюсь, там они в системе существуют ВМЕСТЕ с прикладными таблицами, а не ВМЕСТО них.

а я помню техпроект

давайте по чесноку: в СУБД данные пилят на таблицы исключительно ради того, чтобы одни писатели не конкурировали с другими (кто-то пишет текст, а кто-то ставит лукасы), как только кто-то придумает как правильно мержить структуры типа «документ», так сразу все РСУБД починут в бозе.

Мне приходилось маяться с такими говнорешениями на проде, посылая лучи поноса авторам.

Это одна из говнореализаций спискоты/иерархии поверх реляционщины (таблицы).

поправил, не благодари.

учи теорию реляционных баз

https://www.aoug.at/files/event/106/gallery/AOUG_AK_10_344.jpg

Введение в системы баз данных, Кристофер Дж. Дейта

Читать до конца, большую часть не поймешь, но того что поймешь будет достаточно, чтобы не задавать такие вопросы

по задаче - исполнитель может быть одновременно в нескольких группах, песня может быть одновременно в нескольких альбомах, значит привязывать исполнителя и песни надо к альбомам связями многие ко многим. При этом хорошо бы на уровне БД избежать ситуации, когда исполнитеь привязан к альбому и не привязан к группе. и наоборот. Поэтому добавим таблицу СОСТАВ ГРУППЫ

[мн-мн] - многие ко многим

[fk] - ссылка foreign key

табицы: Группа(id, имя), Состав (id, год с, год по, id_группы), Исполнитель (id, имя), Альбом (id, название), Песня (id, название)

Исполнитель [мн-мн (id исполнителя, id состава)] Состав [fk] Группа

Альбом [fk] Состав

Песня [мн-мн (id_песни, id_альбома)] Альбом

Почему нельзя вообще не ссылаться исполнителем на группу, а ссылаться только на альбом? 1. исполнитель может состоять в группе и не присутствовать ни на одном альбоме. 2. Сначала забиваем в базу исполнителей и группы, а альбомы собираемся добавить когда-нибудь потом.