Как реализовать отношения "многие ко многим" в PostgreSQL?

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

CREATE TABLE product (
  product_id serial PRIMARY KEY  -- implicit primary key constraint
, product    text NOT NULL
, price      numeric NOT NULL DEFAULT 0
);

CREATE TABLE bill (
  bill_id  serial PRIMARY KEY
, bill     text NOT NULL
, billdate date NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE bill_product (
  bill_id    int REFERENCES bill (bill_id) ON UPDATE CASCADE ON DELETE CASCADE
, product_id int REFERENCES product (product_id) ON UPDATE CASCADE
, amount     numeric NOT NULL DEFAULT 1
, CONSTRAINT bill_product_pkey PRIMARY KEY (bill_id, product_id)  -- explicit pk
);

Я сделал несколько настроек:

• Связь n: m обычно выполняется отдельной таблицей - bill_product в этом случае.

• Я добавил столбцы serial в качестве суррогатных первичных ключей. Я очень рекомендую это, потому что название продукта вряд ли уникально. Кроме того, для обеспечения уникальности и ссылки на столбец во внешних ключах гораздо дешевле с 4-байтовым integer, чем с строкой, хранящейся как text или varchar.
  В Postgres 10 или более поздней версии вместо IDENTITY. Подробности:

  • https://blog.2ndquadrant.com/postgresql-10-identity-columns/

• Не используйте имена основных типов данных, например date, как идентификаторы. Хотя это возможно, это плохой стиль и приводит к запутыванию ошибок и сообщений об ошибках. Используйте юридические, строчные, некотируемые идентификаторы. Никогда не используйте зарезервированные слова и избегайте двойных кодовых идентификаторов случая, если вы можете.

• name не является хорошим именем. Я переименовал столбец name таблицы product как product. Это лучше соглашение об именах. В противном случае, когда вы присоединитесь к нескольким таблицам в запросе, который вы делаете много в реляционной базе данных, вы получаете несколько столбцов с именем name и должны использовать псевдонимы столбцов, чтобы разобраться в беспорядке. Это не полезно. Другой распространенный анти-шаблон будет просто id в качестве имени столбца.
  Я не уверен, каким будет имя bill. Может быть, bill_id может быть именем в этом случае.

• price имеет тип numeric для хранения дробных чисел точно как введенный (произвольный тип точности вместо типа с плавающей точкой). Если вы имеете дело исключительно с целыми числами, сделайте это integer. Например, вы можете сэкономить цены как центы.

• amount ("Products" в вашем вопросе) входит в таблицу компоновки bill_product и имеет тип numeric. Опять же, integer, если вы имеете дело исключительно с целыми числами.

• Вы видите внешние ключи в bill_product? Я создал как каскадные изменения (ON UPDATE CASCADE): Если a product_id или bill_id должны измениться, это изменение будет каскадно для всех зависимых записей в bill_product, и ничего не сломается.
  Я также использовал ON DELETE CASCADE для bill_id: если вы удаляете счет, детали будут удалены вместе с ним.
  Не для продуктов: вы не хотите удалять продукт, который использовался в счете. Postgres выдаст ошибку, если вы попытаетесь это сделать. Вы добавили бы еще один столбец в product для обозначения устаревших строк.

• Все столбцы в этом базовом примере заканчиваются на NOT NULL, поэтому значения NULL недопустимы. (Да, все столбцы - столбцы, используемые в первичном ключе, определяются UNIQUE NOT NULL автоматически.) Это потому, что значения NULL не имеют смысла ни в одном из столбцов. Это облегчает жизнь начинающим. Но вы не сможете так легко уйти, вам все равно нужно понимать NULL обработку. Дополнительные столбцы могут позволить значения NULL, функции и объединения могут вводить значения NULL в запросах и т.д.

• Прочитайте главу CREATE TABLE в руководстве.

• Первичные ключи реализуются с уникальным индексом в ключевых столбцах, что делает запросы с условиями на столбцах PK быстрыми. Однако последовательность ключевых столбцов имеет значение в многоколоночных ключах. Поскольку PK на bill_product находится на (bill_id, product_id) в моем примере, вы можете захотеть добавить еще один индекс только для product_id или (product_id, bill_id), если у вас есть запросы, которые ищут a product_id и no bill_id. Подробности:

  • Составной первичный ключ PostgreSQL
  • Является ли составной индекс полезным для запросов в первом поле?
  • Работа индексов в PostgreSQL

• Прочитайте главу о указателях в руководстве.