Нираспарсил, напиши на sql.

Надо добавлять в привязанную таблицу «песни» поле с PK?

Не обязательно.

CREATE TABLE albums (
  id  serial PRIMARY KEY,
  artist varchar(35)  NOT NULL default '',
  name varchar(40) NOT NULL,
  rank int);
CREATE TABLE songs (
  song_id int REFERENCES albums,
  name_song varchar(40) NOT NULL default '',
  path varchar(100) NOT NULL);

надо в таблице songs заводить и поле pk?

Спасибо.

ON DELETE CASCADE я забыл ;)

Почему song_id, а не album_id? Есть какая-нибудь причина использовать varchar(N) вместо text?

я думал, что varchar экономит место.

Только учти, что тебе в будущем PK всё равно скорее всего понадобится, если у тебя приложение не совсем хелловорлд, конечно.

Индексы ещё забыл.

Нет не нужно.

Но блин, ты на скуле пишешь так же непонятно как на русском: song_id int REFERENCES albums

Что за индексы? и просто сайт с музыкой, хелло не хелло... Я сделал на django. Понял, что ничего не понимаю, стал делать на flask и голом postgresql. Стало дольше, но понятнее.

полная строка: song_id int REFERENCES albums ON DELETE CASCADE (по документации, если опустить все, кроме имени таблицы, SQL прдставляет PK сам. Не читаемо, наверное, тогда так)song_id int REFERENCES albums(id) ON DELETE CASCADE

Ты не понял. У тебя поле сонг_ид указывает на альбом.

вывод какой? Надо заводить в каждую таблицу PK или нет? Я правильно понимаю, что РК спасает от воздожных дубликатов? по уникальному номеру можно 1 грохнуть? Или в ссылочных таблицах это лишнее?

Да, название, как заметили выше... Ну так - album_id лучше ;)

Вывод - почитай что нибудь по проектированию бд.

Я вот 2й день и читаю. Одни пишут - пофиг, в стандарте - обязательно.

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

Что именно читаешь? Мне кажется что у тебя проблема с пониманием, что дает та или иная сущность в бд и какие задачи она обычно решает.

Проглядел твоё сообщение с sql.

CREATE TABLE songs (

song_id int REFERENCES albums,

Это же album_id?

+1.

Это простейшая задача, которая в любой нормальной книжке в двух абзацах объясняется.

Читай дальше - обсосали уже

Я сверху вниз с телефона читаю, не особо удобно, вот и пропускаю некоторые моменты.

CREATE TABLE albums (
  id  serial PRIMARY KEY,
  artist varchar(35)  NOT NULL default '',
  name varchar(40) NOT NULL,
  rank int);
CREATE TABLE songs (
  album_id int REFERENCES albums(id) ON DELETE CASCADE,
  name_song varchar(40) NOT NULL default '',
  path varchar(100) NOT NULL);

Как-то так. Вопрос в силе ;) Надо ли добавить поле с PK в таблицу songs?

Смотри, нет обязательного требования это делать. Если там не будет пк, то всё в принципе будет работать.

Тем не менее при написании своего сайта тебе скорее всего очень захочется иметь пк в таблице песен.

Вот бы в двух словах преимущества? Что мне это даст в будущем? Например создание новых таблиц или что-то еще. Буду признателен.

я думал, что varchar экономит место.

Tip: There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

Поясню: в реализации Postgres varchar(N) и text будут занимать одинаковое место, если длина хранимой строки не превышает N символов, только в случае varchar(N) будет ещё дополнительно тратиться процессорное время на проверку длины строки при сохранении. Таким образом дополнительные расходы памяти в случае text будут только тогда, когда ты будешь записывать в базу альбомы и песни с названием длиннее 40 символов (почему так мало?). А теперь возьми калькулятор (листок бумаги, счёты, etc.) и посчитай, сколько в твоей базе примерно будет записей, и какие накладные расходы будут на хранение «длинных» песен и альбомов, и окажется, что в итоге ты сэкономишь каких-нибудь 5 (50, 500 — не суть важно) Мб. Гораздо большей проблемой, с которой ты столкнёшься, будет то, что ты не сможешь хранить песни с названием длиной больше 40 символов.

Ограничение длины поля, конечно, делать нужно (только не 40 символов с случае названия песни/альбома, возьми раз в 3-5 больше). Однако с учётом того, что у тебя скорее всего уже будут валидации на уровне приложения, нет смысла дублировать логику на уровне БД.

Что за индексы?

Как минимум индекс на album_id, если в твоём приложении будет операция «найти песню или вывести все песни в каком-то альбоме». Индекс (обычный или fulltext) на albums.name и songs.name, если ты хочешь искать/сортировать альбомы или песни по названию.

Однако с учётом того, что у тебя скорее всего уже будут валидации на уровне приложения, нет смысла дублировать логику на уровне БД.

Есть. Смысл валидации есть ;) Все поют в голос, что БД «заточена» проверять быстрее, чем много запросов и валидация ЯП.

За пояснение по ограничению спасибо. Стоит ли им тогда пользоваться? что это даёт? В паре слов ;))

не понял я ничего. Я думал, что индексом будет выступать PK альбома.

вывод какой? Надо заводить в каждую таблицу PK или нет? Я правильно понимаю, что РК спасает от воздожных дубликатов? по уникальному номеру можно 1 грохнуть? Или в ссылочных таблицах это лишнее?

Вывод такой: нужно думать головой, а не смотреть на то, ссылочная таблица или нет (hint: никто никогда не выделяет таблицы в отдельную категорию по наличию в них одного поля foreign key). В твоём случае: как ты будешь ссылаться на отдельную песню для удаления/редактирования/отображения? По названию? Могут быть разные песни с одинаковым названием. По комбинации (name, album_id)? В твоей структуре сейчас никто не мешает добавить две песни с одинаковым альбомом и названием.

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

На практике в большинстве случаев можно вообще не думать и лепить primary key во все таблицы (разве что за исключением join tables). Боишься за свободное место? Опять-таки, прикинь, сколько в твоей БД будет записей и какова в общих расходах на хранение БД будет доля первичных ключей.

И нет, primary key не спасает от дубликатов (добавишь ты primary key, и кто помешает тебе создать две абсолютно одинаковые записи, которые будут отличаться только primary key?). От дубликатов спасает unique constraint.

И нет, primary key не спасает от дубликатов

Я видел, что без PK не могли удалить из базы дубликат ;)) в MySQL. Ну да к делу это не относится.

Спасибо Вам и всем большое за пояснения.

Мой вывод - надо, везде на PK. Вы совершенно правы, при наличии одинаковых записей, как обратиться без ключа? Никак.

Вы совершенно правы, при наличии одинаковых записей, как обратиться без ключа?

Тут такое дело, у тебя вообще не должно быть в БД одинаковых записей (за редкими исключениями).

Ой. Получается, мне надо делать проверку CHECK на поля id, name? Это чтобы «по уму»?

Я бы сделал такую структуру (код на работоспособность не проверял):

CREATE TABLE artists (
  id    SERIAL PRIMARY KEY,
  title TEXT NOT NULL
);

-- Создаём индекс для поиска автора по названию.
CREATE INDEX ON artists(title);

CREATE TABLE albums (
  id        BIGSERIAL PRIMARY KEY,
  artist_id INTEGER NOT NULL REFERENCES artists(id)
                              ON DELETE CASCADE,
  title     TEXT NOT NULL,
  rank      INTEGER
);

-- Запрещаем дубликаты: не может быть у одного автора два
-- альбома с одинаковым названием:
CREATE UNIQUE INDEX ON albums(artist_id, title);

-- Создаём индекс для поиска альбомов отдельного автора.
-- Этот индекс избыточен из-за предыдущего индекса (читаем
-- про составные индексы в постгрес), поэтому я его закомментировал.
-- CREATE INDEX ON albums(artist_id);

-- Создаём индекс для поиска альбома по названию:
CREATE INDEX ON albums(title);

CREATE TABLE songs (
  id       BIGSERIAL PRIMARY KEY,
  album_id BIGINT REFERENCES albums(id)
                   ON DELETE CASCADE,
  title    TEXT NOT NULL
);

-- Запрещаем дубликаты: не может быть в одном альбоме две
-- песни с одинаковым названием:
CREATE UNIQUE INDEX ON songs(album_id, title);

-- Создаём индекс для поиска песен по альбому. Он избыточен,
-- поэтому я его закомментировал.
-- CREATE INDEX ON songs(album_id);

-- Создаём индекс для поиска песни по названию:
CREATE INDEX ON songs(title);

-- У песни может быть несколько авторов. Будем считать, что
-- один автор -- главный (это автор альбома). Остальных авторов
-- записываем в следующую таблицу. Это как раз один из случаев,
-- когда без PK можно обойтись -- join table (связь many-to-many),
-- но опять-таки, нужно смотреть по конкретному случаю.
CREATE TABLE featuring (
  artist_id INTEGER NOT NULL REFERENCES artists(id)
                              ON DELETE CASCADE,
  song_id   BIGINT  NOT NULL REFERENCES songs(id),
                              ON DELETE CASCADE
);

-- Ещё индексы: запрещаем дубликаты, ускоряем поиск, мне уже просто лень
-- расписывать.

CREATE UNIQUE INDEX ON featuring(artist_id, song_id);
CREATE INDEX ON featuring(song_id);

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

Получается, мне надо делать проверку CHECK на поля id, name?

Я уже написал: unique constraints (которые в большинстве БД реализуются через уникальные индексы). В качестве примера можно посмотреть мой код выше.

Спасибо. Но я только не понял с индексами. Почему проверку на уникальность не впихнуть в таблицу? Почему в индекс? И второй на вскидку. Индексы мы создаём на основе запросов? Не на каждый чих? ;) Ну да почитаю. Спасибо, с примером будет легче оттолкнуться.

Все поют в голос, что БД «заточена» проверять быстрее, чем много запросов и валидация ЯП.

Ты готов дать оценку, насколько валидация в БД будет быстрее? Сможешь ли ты заметить разницу «на глаз»? Сможешь ли ты в БД реализовать нетривиальную валидацию? Сможешь ли ты восстановиться после ошибки валидации в БД и отобразить для пользователя на странице сообщение об ошибке с учётом того, что БД тебе вернёт строку типа

ERROR:  new row for relation "songs" violates check constraint "songs_title_check"
DETAIL:  Failing row contains (Smells like teen spirit).

а тебе нужно будет вывести сообщение: «Название песни не может быть длиннее 200 символов»?

PK альбома будет выступать индексом в таблице albums, но искать песни по ID альбома ты будешь не в таблице albums, а в таблице songs.

Но я только не понял с индексами. Почему проверку на уникальность не впихнуть в таблицу?

Как ты хочешь это сделать? С помощью CHECK, что ли?

Проверку уникальности действительно можно запихнуть в таблицу с помощью table/column unique constraints.

На примере таблицы albums это можно сделать так:

CREATE TABLE albums (
  id        BIGSERIAL PRIMARY KEY,
  artist_id INTEGER NOT NULL REFERENCES artists(id)
                              ON DELETE CASCADE,
  title     TEXT NOT NULL,
  rank      INTEGER,

  UNIQUE(artist_id, title)
);

При этом UNIQUE constraint в реализации Postgres и большинства РСУБД развернётся в уникальный индекс, который я создавал руками.

Почему в индекс?

Я это сделал для наглядности. На практике с точки зрения семантики лучше использовать вариант с column/table unique constraint.

Индексы мы создаём на основе запросов? Не на каждый чих? ;)

Да. К примеру, если тебе не нужно сортировать/искать авторов по имени, то и индекст на имя не нужен.

Дополню: один из немногих случаев валидации, которую имеет смысл вынести в БД, это валидация уникальности, т.к. её хрен реализуешь в приложении из-за race conditions.

Вот и вырисовалась общая картина. Очень наглядно. Спасибо. Теперь немного дочитать и вперед! К свершениям!

Недавно попёр меня folk. После электронщины, так пару песен у него нормално слушается ;) https://www.youtube.com/watch?v=AzUZhdVHSUo

Ну и ну...

Советую автору (и всем прочим, да) задавать свои вопросы по СУБД на тематических ресурсах, например на sql.ru, т.к. здесь в ответах на них можно прочитать много всего «удивительного»:

скорее всего уже будут валидации на уровне приложения, нет смысла дублировать логику на уровне БД.

primary key не спасает от дубликатов

Это как раз один из случаев, — когда без PK можно обойтись — join table

надо в таблице songs заводить и поле pk?

Если таблиц так и останется две - не обязательно. А вот если есть хотя бы небольшая вероятность разрастания БД и появления таблиц, ссылающихся на songs - лучше сразу добавить PK.

Если б я делал реальную (не учебную) БД по музыке - я бы сразу вместо текстового поля artist сделал отдельную таблицу исполнителей, более того, там ещё связь «многие ко многим» напрашивается...

Например создание новых таблиц

Например, да.

Да, анониим, ты прав ;)

Спасибо. А что даёт такое разбиение?

Если ты про моё последнее предложение - то, например, нормальный быстрый поиск всех альбомов одного исполнителя.

Хорошо. Получается, таблица «Исполнитель» -> таблица «Альбом» -> таблица «Песня». Такая структура позволит ускорить поиск?

Это как раз один из случаев, — когда без PK можно обойтись — join table

Тут я тупанул, вместо составного уникального индекса нужно было бы сделать составной primary key, да. Под «без PK можно обойтись» я имел в виду суррогатный PK («счётчик»).

По остальным пунктам пояснения будут, в чём я не прав?