Ответ 1
Знакомство с SuperTypes и SubTypes
Я предлагаю вам использовать супертипы и подтипы. Сначала создайте таблицы PartyType и Party:
CREATE TABLE dbo.PartyType (
PartyTypeID int NOT NULL identity(1,1) CONSTRAINT PK_PartyType PRIMARY KEY CLUSTERED
Name varchar(32) CONSTRAINT UQ_PartyType_Name UNIQUE
);
INSERT dbo.PartyType VALUES ('Person'), ('Business');
Supertype
CREATE TABLE dbo.Party (
PartyID int identity(1,1) NOT NULL CONSTRAINT PK_Party PRIMARY KEY CLUSTERED,
FullName varchar(64) NOT NULL,
BeginDate smalldatetime, -- DOB for people or creation date for business
PartyTypeID int NOT NULL
CONSTRAINT FK_Party_PartyTypeID FOREIGN KEY REFERENCES dbo.PartyType (PartyTypeID)
);
подтипов
Затем, если есть столбцы, которые являются уникальными для Лица, создайте таблицу Person только с такими:
CREATE TABLE dbo.Person (
PersonPartyID int NOT NULL
CONSTRAINT PK_Person PRIMARY KEY CLUSTERED
CONSTRAINT FK_Person_PersonPartyID FOREIGN KEY REFERENCES dbo.Party (PartyID)
ON DELETE CASCADE,
-- add columns unique to people
);
И если есть столбцы, которые являются уникальными для Бизнесов, создайте таблицу Business только с этими:
CREATE TABLE dbo.Business (
BusinessPartyID int NOT NULL
CONSTRAINT PK_Business PRIMARY KEY CLUSTERED
CONSTRAINT FK_Business_BusinessPartyID FOREIGN KEY REFERENCES dbo.Party (PartyID)
ON DELETE CASCADE,
-- add columns unique to businesses
);
Использование и заметки
Наконец, ваша таблица Asset будет выглядеть примерно так:
CREATE TABLE dbo.Asset (
AssetID int NOT NULL identity(1,1) CONSTRAINT PK_Asset PRIMARY KEY CLUSTERED,
PartyID int NOT NULL
CONSTRAINT FK_Asset_PartyID FOREIGN KEY REFERENCES dbo.Party (PartyID),
AssetTag varchar(64) CONSTRAINT UQ_Asset_AssetTag UNIQUE
);
Отношение, которое столбец "Супертип" разделяет с таблицами подтипов "Бизнес и человек" - "один на нуль-один". Теперь, в то время как подтипы обычно не имеют соответствующей строки в другой таблице, в этой конструкции есть возможность иметь Сторону, которая заканчивается в обеих таблицах. Однако вам действительно может понравиться следующее: иногда человек и бизнес почти взаимозаменяемы. Если это не полезно, в то время как триггер для принудительного выполнения этого будет довольно легко выполнен, лучшим решением является, вероятно, добавить столбец PartyTypeID в таблицы подтипов, сделав его частью ПК и FK, и поставьте ограничение CHECK на PartyTypeID.
Красота этой модели заключается в том, что, когда вы хотите создать столбец, который имеет ограничение для бизнеса или человека, вы создаете ограничение для соответствующей таблицы вместо таблицы сторон.
Кроме того, при необходимости может оказаться полезным включение каскадного удаления в ограничениях, а также триггер INSTEAD OF DELETE в таблицах подтипов, которые вместо этого удаляют соответствующие идентификаторы из таблицы супертипов (это не гарантирует, что строки супертипа, которые не имеют строки подтипа). Эти запросы очень просты и работают на уровне всей строки-существующего или не существующего уровня, что, на мой взгляд, является гигантским улучшением по сравнению с любой конструкцией, которая требует проверки согласованности столбцов.
Также обратите внимание, что во многих случаях столбцы, которые, по вашему мнению, должны входить в одну из подтипов, действительно могут быть объединены в таблицу супертипов, например номер социального страхования. Назовите это TIN (идентификационный номер налогоплательщика), и он работает как для бизнеса, так и для людей.
Идентификация столбца идентификатора
Вопрос о том, следует ли вызывать столбец в таблице Person PartyID, PersonID или PersonPartyID, является вашим собственным предпочтением, но я думаю, что лучше всего назвать эти PersonPartyID или BusinessPartyID - терпимости к стоимости более длинного имени, это позволяет избежать двух типов путаницы. Например, кто-то, незнакомый с базой данных, видит BusinessID и не знает, что это PartyID, или видит PartyID, и не знает, что он ограничен внешним ключом только тем, что находится в таблице Business.
Если вы хотите создавать представления для таблиц Party и Business, они могут даже быть материализованными представлениями, поскольку это простое внутреннее соединение, и там вы можете переименовать столбец PersonPartyID в PersonID, если бы вы были действительно так склонны (хотя я бы не стал). Если это имеет большое значение для вас, вы можете даже создавать триггеры INSTEAD OF INSERT и INSTEAD OF UPDATE для этих представлений, чтобы обрабатывать вставки для этих двух таблиц для вас, делая представления полностью похожими на свои собственные таблицы на многие прикладные программы.
Создание вашей предлагаемой проектной работы как есть
Кроме того, я не хочу упоминать об этом, но если вы хотите иметь ограничение в предлагаемом дизайне, который обеспечивает заполнение только одного столбца, вот код для этого:
ALTER TABLE dbo.Assets
ADD CONSTRAINT CK_Asset_PersonOrBusiness CHECK (
CASE WHEN PersonID IS NULL THEN 0 ELSE 1 END
+ CASE WHEN BusinessID IS NULL THEN 0 ELSE 1 END = 1
);
Однако я не рекомендую это решение.
Заключительные мысли
Естественным третьим подтипом, который нужно добавить, является организация, в смысле чего-то, к чему могут принадлежать люди и предприятия. Супертип и подтип также элегантно решают клиента/сотрудника, клиента/поставщика и другие проблемы, подобные тому, который вы представили.
Будьте осторожны, чтобы не путать "Is-A" с "Acts-As-A". Вы можете сказать, что участник - это клиент, заглядывая в таблицу заказов или просматривая счет заказа, и может вообще не нуждаться в таблице Customer. Также не путайте идентичность с жизненным циклом: в конечном итоге может быть продан автомобиль напрокат, но это прогресс в жизненном цикле, и его следует обрабатывать с данными столбцов, а не с таблицей - автомобиль не запускается как RentalCar и превратиться в ForSaleCar позже, это автомобиль все время. Или, может быть, RentalItem, может быть, бизнес тоже будет арендовать другие вещи. Вы получаете идею.
Возможно, нет необходимости иметь таблицу PartyType. Тип партии может определяться наличием строки в соответствующей таблице подтипов. Это также позволило бы избежать потенциальной проблемы PartyTypeID, не соответствующей присутствию таблицы подтипов. Одна из возможных реализаций - сохранить таблицу PartyType, но удалите PartyTypeID из таблицы Party, а затем в представлении в таблице Party верните правильный PartyTypeID, на основе которого в таблице подтипов будет соответствующая строка. Это не будет работать, если вы решите разрешить сторонам быть оба подтипами. Затем вы просто придерживаетесь представлений подтипов и знаете, что одно и то же значение BusinessID и PersonID относятся к одной и той же стороне.
Дальнейшее чтение этого шаблона
Подробнее см. Универсальная персональная и организационная модель данных для более полного и теоретического лечения.
Недавно я нашел следующие статьи полезными для описания некоторых альтернативных подходов к моделированию наследования в базе данных. Хотя это специфично для Microsoft ORM Framework ORM, нет причин, по которым вы не могли бы реализовать их самостоятельно в любой разработке БД:
- Таблица для иерархии
- Table Per Type (это то, что я выступаю выше как полностью только полностью реализуемый метод реализации наследования в база данных)
- Таблица для конкретного класса
- Или более краткий обзор этих трех способов: Как выбрать стратегию наследования
P.S. Я неоднократно переключал свое мнение о столбце, обозначающем идентификаторы в таблицах подтипов, из-за того, что у меня больше опыта под моим поясом.