Роль отношения "один-ко-многим" в базе данных

Question

Работаю над проектом, где хочу реализовать реферальную систему бонусов. Раньше я создавал две таблицы users и referrers и просто записывал там необходимые данные. Недавно я начал изучать создание отношений "один-ко-многим". Сделал всё по гайдам, но я не понимаю.

Что даёт использование такой связи? В чём разница, если я буду вести 2 никак не связанные таблицы
Как делать запросы к таким таблицам?

Таблица users устроена подобным образом:

id	name
1	Ivan
2	John

А referrers:

referrer_id	referral_id
1	2

Код выглядит следующим образом:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE referrers (
  referrer_id int NOT NULL,
  referral_id int NOT NULL,
  FOREIGN KEY (referrer_id) REFERENCES users (id)
);

Answer 1

Что даёт использование такой связи? В чём разница, если я буду вести 2 никак не связанные таблицы

Контроль за целостностью данных. Наличие связи не позволит внести на стороне "много" запись, ссылающуюся на несуществующую запись на стороне "один".

Кроме того, связь обеспечивает каскадные изменения. Если изменяется связующее поле на стороне "один", автоматически изменятся и все значения на стороне "много". Либо операция приведёт к ошибке, если при этой операции нарушится какое-либо ограничение. То же относится и к удалению.

Главное, что надо усвоить: внешний ключ - это правило контроля, по которому работает подсистема контроля целостности и непротиворечивости данных СУБД. А вовсе даже не индекс или иная структура хранения данных.

Как делать запросы к таким таблицам?

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

Answer 2

Такой подход в построении таблиц даёт надёжность и целостность данных.

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

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

Если же связи у таблиц нет, то при изменении допустим какого-либо пользователя из users, данные в таблице referrers не изменятся и придётся идти вручную их менять, что при больших объёмах обработки данных невозможно.

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

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

CREATE TABLE referrers (
    referrer_id INT,
    referral_id INT,
    FOREIGN KEY (referrer_id) REFERENCES users(id),
    FOREIGN KEY (referral_id) REFERENCES users(id)
);

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

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

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

БЛОГ НА HUSL

Роль отношения "один-ко-многим" в базе данных

Ответы (2 шт):