Дизайн базы данных - статьи, посты в блогах, фотографии, рассказы

Question

Я разрабатываю базу данных для веб-сайта, в которой будет представлено как минимум 4 различных типа объектов (статьи, записи в блогах, фотографии, истории), каждый из которых имеет достаточно разные требования к данным, чтобы гарантировать наличие собственных таблиц. Мы хотим, чтобы пользователи могли оставлять комментарии для любого из этих типов. Требования к данным для комментариев просты и не зависят от того, к чему относится комментарий (т. Е. Просто тело комментария и электронная почта автора).

Я хочу избежать избыточности создания и управления 4+ отдельными таблицами для комментариев, поэтому я хотел бы иметь возможность хранить все комментарии в одной таблице, возможно указав отношение через 2 столбца: один для обозначения родительской сущности и один для идентификатора родительской строки.

но я не понимаю, как тогда реализовать внешние ключи, поскольку внешние ключи устанавливают связь между 2 и только 2 таблицами (верно?).

Итак, учитывая все вышесказанное, какой будет наилучший подход?

Answer 1

Вот один из способов реализации таблиц супертипа / подтипа для вашего приложения.

Во-первых, таблица супертипа. Он содержит все столбцы, общие для всех подтипов.

CREATE TABLE publications (
  pub_id INTEGER NOT NULL PRIMARY KEY,
  pub_type CHAR(1) CHECK (pub_type IN ('A', 'B', 'P', 'S')),
  pub_url VARCHAR(64) NOT NULL UNIQUE,
  CONSTRAINT publications_superkey UNIQUE (pub_id, pub_type)
);

Далее пара таблиц подтипов.

CREATE TABLE articles (
  pub_id INTEGER NOT NULL,
  pub_type CHAR(1) DEFAULT 'A' CHECK (pub_type = 'A'),
  placeholder CHAR(1) NOT NULL, -- placeholder for other attributes of articles
  PRIMARY KEY (pub_id, pub_type),
  FOREIGN KEY (pub_id, pub_type) REFERENCES publications (pub_id, pub_type)
);

CREATE TABLE stories (
  pub_id INTEGER NOT NULL,
  pub_type CHAR(1) DEFAULT 'S' CHECK (pub_type = 'S'),
  placeholder CHAR(1) NOT NULL, -- placeholder for other attributes of stories
  PRIMARY KEY (pub_id, pub_type),
  FOREIGN KEY (pub_id, pub_type) REFERENCES publications (pub_id, pub_type)
);

Ограничения CHECK() и FOREIGN KEY в этих таблицах подтипов не позволяют ссылкам на строки неверного типа в супертипе. Он эффективно разделяет значения pub_id среди подтипов, гарантируя, что любой заданный pub_id может появиться в одной и только одной из таблиц подтипов. Вот почему вам нужно ограничение PRIMARY KEY или NOT NULL UNIQUE для пары столбцов {публикации.pub_id, публикации.pub_type}.

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

CREATE TABLE comments (
  pub_id INTEGER NOT NULL REFERENCES publications (pub_id),
  comment_timestamp TIMESTAMP NOT NULL DEFAULT now(),
  commenter_email VARCHAR(10) NOT NULL, -- Only allow people who have 
                                        -- really short email addresses
  comment_text VARCHAR(30) NOT NULL,    -- Keep 'em short!
  PRIMARY KEY (pub_id, comment_timestamp, commenter_email)
);

Добавьте немного данных.

INSERT INTO publications VALUES
(1,'A', 'url 1 goes here'),
(2,'A', 'url 2 goes here'),
(3,'S', 'url 3 goes here');

INSERT INTO articles VALUES
(1,'A', 'A'),
(2,'A', 'B');

INSERT INTO stories VALUES
(3,'S', 'A');

INSERT INTO comments VALUES
(1, now(), '[e-mail]','You''re stupid'),
(1, now(), '[e-mail]', 'You''re stupid, too!');

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

CREATE VIEW articles_all AS
SELECT P.*, A.placeholder
FROM publications P
INNER JOIN articles A ON (A.pub_id = P.pub_id)

Вы можете предпочесть такие имена, как "ключ-публикации" вместо "статьи-все".

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

SELECT A.*, C.*
FROM articles_all A
LEFT JOIN comments C ON (A.pub_id = C.pub_id)
WHERE A.pub_id = 1;

Вы, вероятно, на самом деле не сделали бы этого для веб-интерфейса, потому что dbms должен был бы возвращать 'n' копий статьи, где 'n' равно количеству комментариев. Но имеет смысл делать это в некоторых приложениях. В приложениях, где это имеет смысл, вы будете использовать одно обновляемое представление для каждого подтипа, а код приложения будет использовать обновляемые представления в большинстве случаев.

---

Более распространенное бизнес-приложение супертипа / подтипа включает "Стороны" (супертип), "Организации" и "Физические лица" (подтипы, неофициально компании и людей. Адреса, такие как "комментарии" в приведенном выше примере, связаны с супертип, потому что все подтипы (организации и частные лица) имеют адреса.

Answer 2

Вы можете использовать супертип / подтип в дизайне БД, чтобы избежать этой проблемы. Создайте супер-тип для изображений, видео, заметок, а затем создайте ссылку на супер-тип. Сохраните все общие столбцы в таблице супертипов.

Вот несколько ссылок на несколько похожих вопросов / ответов с моделями:

• Один
• Два
• Три
• четыре

Answer 3

На мой взгляд, вам лучше иметь 4+ отдельных таблиц для комментариев. Или вы могли бы иметь соединительные столы. Одна таблица для всех комментариев... например: таблица блогов, таблица комментариев, таблица blog_comment. это позволит вам иметь ваши внешние ключи.

Blog
--------
Blog_id
{other fields}

Blog_Comment
--------------
Blog_id
Comment_id


Comment
------------
Comment_id
{other fields}