Универсальный дизайн Таблицы базы данных

Вариант 2 не хорошее решение для реляционной базы данных. Это называется полиморфными ассоциациями (как упомянул @Daniel Vassallo) и нарушает фундаментальное определение отношения.

Например, предположим, что у вас есть ResourceId 1234 в двух разных строках. Представляют ли они один и тот же ресурс? Это зависит от того, совпадает ли CommentTypeId в этих двух строках. Это нарушает концепцию типа в отношении. См. SQL and Relational Theory C. J. Date для получения более подробной информации.

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

Я бы решил эту проблему с помощью решения, которое @mdma вкратце упоминает (но затем игнорирует):

CREATE TABLE Commentable (
  ResourceId INT NOT NULL IDENTITY,
  ResourceType INT NOT NULL,
  PRIMARY KEY (ResourceId, ResourceType)
);

CREATE TABLE Documents (
  ResourceId INT NOT NULL,
  ResourceType INT NOT NULL CHECK (ResourceType = 1),
  FOREIGN KEY (ResourceId, ResourceType) REFERENCES Commentable
);

CREATE TABLE Projects (
  ResourceId INT NOT NULL,
  ResourceType INT NOT NULL CHECK (ResourceType = 2),
  FOREIGN KEY (ResourceId, ResourceType) REFERENCES Commentable
);

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

CREATE TABLE Comments (
  CommentId INT IDENTITY PRIMARY KEY,
  ResourceId INT NOT NULL,
  ResourceType INT NOT NULL,
  FOREIGN KEY (ResourceId, ResourceType) REFERENCES Commentable
);

Теперь комментарии ссылаются на комментируемые ресурсы с принудительной ссылочной целостностью. Данный комментарий может ссылаться только на один тип ресурса. Нет возможности аномалий или конфликтующих идентификаторов ресурсов.

Подробнее о полиморфных ассоциациях я рассказываю в своей презентации Практические объектно-ориентированные модели в SQL и в моей книге SQL Antipatterns .

В продолжение ответа @OMG Ponies то, что вы описываете во втором примере, называется полиморфной ассоциацией , где внешний ключ ] ResourceID может ссылаться на строки в более чем одной таблице. Однако в базах данных SQL ограничение внешнего ключа может ссылаться только на одну таблицу. База данных не может применить внешний ключ в соответствии со значением в CommentTypeID .

Возможно, вас заинтересует следующий пост о переполнении стека, чтобы узнать об одном решении этой проблемы:

• MySQL - Ограничения условного внешнего ключа

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

Для подхода ResourceID для работы со ссылочной целостностью вам потребуется таблица Resource и внешний ключ ResourceID во всем вашем документе. , Project и т. Д. Сущностей (или используйте таблицу сопоставления.) Создание ResourceID универсальным инструментом, который может быть documentID, projectID и т. Д., Не является хорошим решением, поскольку его нельзя использовать для разумной индексации или ограничение внешнего ключа.

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

Comment
-------
CommentID
CommentText
...etc 

DocumentComment
---------------
DocumentID
CommentID

ProjectComment
--------------
ProjectID
CommentID

Если разрешен только один комментарий, вы добавляете уникальное ограничение на внешний ключ для объекта (DocumentID, ProjectID и т. Д.). Это гарантирует, что для данного элемента может быть только одна строка, а значит, только один комментарий. Вы также можете гарантировать, что комментарии не передаются, используя уникальное ограничение для CommentID.

РЕДАКТИРОВАТЬ: Интересно, что это почти аналогично нормализованной реализации ResourceID - замените «Comment» в имени таблицы на «Resource» и измените «CommentID» на «ResourceID», и у вас будет структура, необходимая для связывания ResourceID. с каждым ресурсом. Затем вы можете использовать одну таблицу ResourceComment.

Если будут другие объекты, связанные с любым типом ресурса (например, сведения об аудите, права доступа и т. Д.)), то можно использовать таблицы сопоставления ресурсов, поскольку они позволяют добавлять нормализованные комментарии и любые другие объекты, связанные с ресурсами.

Приложение ломбарда:

У меня есть отдельные таблицы для операций ссуды, покупки, инвентаря и продажи. Строки каждой таблицы соединены с соответствующими строками клиентов следующим образом:

customer.pk [serial] = loan.fk [integer];
                     = purchase.fk [integer];
                     = inventory.fk [integer];
                     = sale.fk [integer]; 

Я объединил четыре таблицы в одну таблицу под названием «транзакция», где столбец:

transaction.trx_type char (1) {L = Loan, P = Покупка, I = инвентарь, S = продажа}

Сценарий:

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

Я разработал общую таблицу транзакций, где, например:

transaction.main_amount DECIMAL (7,2)

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

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

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

Прочтите о нормализации базы данных.

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

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

Я бы не пошел ни с одним из этих решений. В зависимости от некоторых особенностей ваших требований вы можете использовать таблицу супертипа:

CREATE TABLE Commentable_Items (
    commentable_item_id    INT    NOT NULL,
    CONSTRAINT PK_Commentable_Items PRIMARY KEY CLUSTERED (commentable_item_id))
GO
CREATE TABLE Projects (
    commentable_item_id    INT    NOT NULL,
    ... (other project columns)
    CONSTRAINT PK_Projects PRIMARY KEY CLUSTERED (commentable_item_id))
GO
CREATE TABLE Documents (
    commentable_item_id    INT    NOT NULL,
    ... (other document columns)
    CONSTRAINT PK_Documents PRIMARY KEY CLUSTERED (commentable_item_id))
GO

Если каждый элемент может иметь только один комментарий, а комментарии не являются общими (т. Е. Комментарий может принадлежать только одной сущности), то вы можете просто поместить комментарии в таблицу Commentable_Items. В противном случае вы можете связать комментарии из этой таблицы с помощью внешнего ключа.

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

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

CREATE TABLE Comments (
    comment_id    INT            NOT NULL,
    comment_text  NVARCHAR(MAX)  NOT NULL,
    CONSTRAINT PK_Comments PRIMARY KEY CLUSTERED (comment_id))
GO
CREATE TABLE Document_Comments (
    document_id    INT    NOT NULL,
    comment_id     INT    NOT NULL,
    CONSTRAINT PK_Document_Comments PRIMARY KEY CLUSTERED (document_id, comment_id))
GO
CREATE TABLE Project_Comments (
    project_id     INT    NOT NULL,
    comment_id     INT    NOT NULL,
    CONSTRAINT PK_Project_Comments PRIMARY KEY CLUSTERED (project_id, comment_id))
GO

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

Именно все эти «маленькие» решения будут влиять на конкретные ПК и ФК. Мне нравится такой подход, потому что в каждой таблице четко указано, что это такое. В базах данных это обычно лучше, чем иметь «универсальные» таблицы/решения.

Из предложенных вами вариантов я бы выбрал номер 2.

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

Если вы несете одинаковые данные обо всех комментариях, независимо от того, к чему они относятся, я бы проголосовал против создания нескольких таблиц комментариев. Может быть, комментарий - это просто «суть» и текст, но если у вас сейчас нет других данных, скорее всего, у вас будут: дата ввода комментария, идентификатор пользователя человека, который его сделал, и т. Д. С несколькими таблицами вы должны повторить все эти определения столбцов для каждой таблицы.

Как уже отмечалось, использование единственного ссылочного поля означает, что вы не можете наложить на него ограничение внешнего ключа. Это очень плохо, но ничего не ломается, это просто означает, что вам нужно выполнить проверку с помощью триггера или в коде. А если серьезно, то присоединиться становится сложно. Вы можете просто сказать «из комментария присоединиться к документу с помощью (documenttid)». Вам нужно сложное соединение, основанное на значении поля типа.

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

Лично я был бы готов услышать дальнейшее обсуждение плюсов и минусов.