Что-то вроде наследования в дизайне базы данных

Question

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

Вы можете создать три отдельные таблицы: SpeedboatTests, CarTests и GokartTests. Но многие ваши столбцы будут одинаковыми в каждой таблице (например, идентификатор сотрудника человека, который выполнил тест, направление столкновения (спереди, сбоку, сзади) и т. Д.). Тем не менее, множество столбцов будет отличаться, поэтому вы не хотите просто помещать все тестовые данные в одну таблицу, потому что у вас будет довольно много столбцов, которые всегда будут нулевыми для скоростных катеров, довольно много, которые всегда будут быть нулевым для автомобилей, и немало таких, которые всегда будут нулевыми для картинга.

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

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

Быстроходные катера
id | col_about_speedboats_but_not_tests1 | col_about_speedboats_but_not_tests2

Машины
id | col_about_cars_but_not_tests1 | col_about_cars_but_not_tests2

Gokarts
id | col_about_gokarts_but_not_tests1 | col_about_gokarts_but_not_tests2

тесты
id | тип | id_in_type | col_about_all_tests1 | col_about_all_tests2
(id_in_type будет ссылаться на столбец id одной из следующих трех таблиц,
в зависимости от значения типа)

SpeedboatTests
id | speedboat_id | col_about_speedboat_tests1 | col_about_speedboat_tests2

CarTests
id | car_id | col_about_car_tests1 | col_about_car_tests2

GokartTests
id | gokart_id | col_about_gokart_tests1 | col_about_gokart_tests2

Что хорошего / плохого в этой структуре и какой предпочтительный способ реализации чего-то подобного?

Что, если есть также некоторая информация, которая относится ко всем транспортным средствам, которые вы хотели бы иметь в таблице транспортных средств? Будет ли таблица CarTests выглядеть примерно так...

id | Vehicle_id | ...

С таблицей транспортных средств, как это:
id | тип | id_in_type
(с идентификатором id_in_type, указывающим на идентификатор катера, машины или картинга)

Кажется, это просто королевский беспорядок. Как ДОЛЖНО быть настроено что-то подобное?

Answer 1

type а также id_in_type Дизайн называется Полиморфные Ассоциации. Этот дизайн нарушает правила нормализации несколькими способами. Если ничего другого, это должен быть красный флаг, который вы не можете объявить реальным ограничением внешнего ключа, потому что id_in_type может ссылаться на любую из нескольких таблиц.

Вот лучший способ определения ваших таблиц:

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

Вот пример DDL:

CREATE TABLE Vehicles (
 vehicle_id INT AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE Speedboats (
 vehicle_id INT PRIMARY KEY,
 col_about_speedboats_but_not_tests1 INT,
 col_about_speedboats_but_not_tests2 INT,
 FOREIGN KEY(vehicle_id) REFERENCES Vehicles(vehicle_id)
);

CREATE TABLE Cars (
 vehicle_id INT PRIMARY KEY,
 col_about_cars_but_not_tests1 INT,
 col_about_cars_but_not_tests2 INT,
 FOREIGN KEY(vehicle_id) REFERENCES Vehicles(vehicle_id)
);

CREATE TABLE Gokarts (
 vehicle_id INT PRIMARY KEY,
 col_about_gokarts_but_not_tests1 INT,
 col_about_gokarts_but_not_tests2 INT,
 FOREIGN KEY(vehicle_id) REFERENCES Vehicles(vehicle_id)
);

CREATE TABLE Tests (
 test_id INT AUTO_INCREMENT PRIMARY KEY,
 col_about_all_tests1 INT,
 col_about_all_tests2 INT
);

CREATE TABLE SpeedboatTests (
 test_id INT PRIMARY KEY,
 vehicle_id INT NOT NULL,
 col_about_speedboat_tests1 INT,
 col_about_speedboat_tests2 INT,
 FOREIGN KEY(test_id) REFERENCES Tests(test_id),
 FOREIGN KEY(vehicle_id) REFERENCES Speedboats(vehicle_id)
);

CREATE TABLE CarTests (
 test_id INT PRIMARY KEY,
 vehicle_id INT NOT NULL,
 col_about_car_tests1 INT,
 col_about_car_tests2 INT,
 FOREIGN KEY(test_id) REFERENCES Tests(test_id),
 FOREIGN KEY(vehicle_id) REFERENCES Cars(vehicle_id)
);

CREATE TABLE GokartTests (
 test_id INT PRIMARY KEY,
 vehicle_id INT NOT NULL,
 col_about_gokart_tests1 INT,
 col_about_gokart_tests2 INT,
 FOREIGN KEY(test_id) REFERENCES Tests(test_id),
 FOREIGN KEY(vehicle_id) REFERENCES Gokarts(vehicle_id)
);

Вы могли бы альтернативно объявить Tests.vehicle_id какие ссылки Vehicles.vehicle_id и избавиться от внешних ключей vehicle_id в каждой таблице подтипов теста, но это разрешит аномалии, такие как тест на скоростном катере, который ссылается на идентификатор gokart.

Answer 2

Я думаю, что для сопоставления иерархий наследования с таблицами базы данных Мартин Фаулер довольно хорошо излагает альтернативы в своей книге "Шаблоны архитектуры корпоративных приложений".

http://martinfowler.com/eaaCatalog/singleTableInheritance.html

http://martinfowler.com/eaaCatalog/classTableInheritance.html

http://martinfowler.com/eaaCatalog/concreteTableInheritance.html

Если количество дополнительных полей / столбцов мало для подклассов, то наследование одной таблицы обычно является наиболее простым для работы.

Если вы используете PostgreSQL для своей базы данных и хотите привязать себя к функции, специфичной для базы данных, она напрямую поддерживает наследование таблиц:

http://www.postgresql.org/docs/8.3/static/ddl-inherit.html

Answer 3

Я бы разбил его на разные таблицы, например Vehicle (ID, тип и т. Д.) VehicleAttributes ()VehicleID, AttributeID, Value), CrashTestInfo(VehicleID, CrashtestID, Date и т. Д.) CrashtestAttributes(CrashTestID, AttributeID, Value)

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

Answer 4

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

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

Answer 5

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

Лучшие статьи, IMO, обсуждают ген-спецификацию с точки зрения моделирования ER. Если вы знаете, как преобразовать модель ER в реляционную модель, а затем в таблицы SQL, вы будете знать, что делать, когда они покажут вам, как моделировать gen-spec в ER.

Если вы просто гуглите по "gen-spec", большая часть того, что вы увидите, является объектно-ориентированной, а не реляционной. Этот материал также может быть полезен, если вы знаете, как преодолеть несоответствие реляционного импеданса объекта.

Answer 6

Ваш дизайн разумный и соответствует правильным правилам нормализации. Возможно, вам не хватает таблицы транспортных средств с идентификатором и типом транспортного средства (т. Е. "Родитель" для быстроходных катеров, автомобилей и гокартов... там, где вы храните такие вещи, как "DesignedByUserId"). Между таблицей "Транспортное средство" и таблицей "Быстроходные катера" существует взаимно-однозначное отношение, а между "Транспортным средством" и "Скоростной катер / Автомобили /GoKarts" существует отношение "1 и только 1" (т. Е. Транспортное средство может иметь только 1 запись для быстроходного катера, машины или картинг)... хотя большинство БД не предлагают для этого простой механизм правоприменения.

Одно правило нормализации, которое помогает идентифицировать такие вещи, состоит в том, что поле должно зависеть только от первичного ключа таблицы. В сводной таблице, в которой результаты испытаний на катере, автомобилях и гокартах хранятся вместе, поля, относящиеся к автомобилям, зависят не только от даты испытания, но также от идентификатора транспортного средства и типа транспортного средства. Первичным ключом для таблицы результатов испытаний является дата теста + идентификатор транспортного средства, а тип транспортного средства не делает строку данных теста уникальной (т. Е. Есть ли возможность провести тест 01.01.200912:30 вечера на одном конкретном транспортном средстве это и катер, и машина... Нет... не может быть сделано).

Я не очень хорошо объясняю правило нормализации... но правила 3/4/5-й нормальных форм меня всегда смущают, когда я читаю формальные описания. Одно из них (3/4/5) касается полей, зависящих от первичного ключа и только первичного ключа. Правило предполагает, что первичный ключ был правильно идентифицирован (неправильно определить первичный ключ слишком просто).