SQL: нормализация базы данных при сохранении ограничений
Предположим, у меня есть следующие таблицы:
____________________ ____________________
| Organisms | | Species |
|--------------------| |--------------------|
|OrganismId (int, PK)| |SpeciesId (int, PK) |
|SpeciesId (int, FK) |∞---------1|Name (varchar) |
|Name (varchar) | |____________________|
|____________________| 1
1 |
| |
| |
∞ ∞
______________________ ____________________ _______________
| OrganismPropsValues | | SpeciesProps | | Props |
|----------------------| |--------------------| |---------------|
|OrganismId (int, FK) | |PropId (int,PK,FK) | ∞-----1|PropId (int,PK)|
|PropId (int, FK) | |SpeciesId(int,PK,FK)| |Name (varchar) |
|Value (varchar) | |____________________| |_______________|
|______________________| 1
∞ |
| |
-----------------------------------------------------------
Краткое объяснение того, что я пытаюсь изобразить здесь: предположим, у нас есть список видов, таких как кошка, собака, человек и т. Д. У нас также есть набор свойств (сокращенно Props, чтобы мне было удобнее вписать его в диаграмму), которые относятся к некоторым, но не обязательно ко всем видам - например, это может быть длина хвоста (для видов с хвостами), цвет глаз (для видов с глазами) и т. д.
SpeciesProps - это таблица компоновщика, которая определяет, какие свойства применимы к какому виду - поэтому здесь у нас есть {Человек, Цвет глаз}, {Собака, Цвет глаз}, {Кошка, Цвет глаз}, {Собака, Длина хвоста}, {Кошка Длина хвоста. У нас нет {Human, Tail Length}, потому что Tail Length, очевидно, не является допустимым свойством, применимым к человеку.
Таблица "Организмы" содержит фактические "реализации" вида. Итак, здесь у нас могут быть {Человек, Боб}, {Собака, Руфус} и {Кошка, Феликс}.
Теперь моя проблема: в таблице OrganismPropsValues я хочу сохранить "значения" свойств для каждого организма - например, для Боба я хочу сохранить {Bob, Eye Color, Blue}. Для Руфуса я бы хотел сохранить {Руфус, Цвет глаз, Коричневый} и {Руфус, Длина хвоста, 20} (аналогично Феликсу). Моя проблема, однако, заключается в том, что в схеме, которую я подробно описал, вполне возможно хранить {Bob, Tail Length, 10}, даже если кортеж {Human, Tail Length} не существует в SpeciesProps. Как я могу изменить эту схему, чтобы я мог применять ограничения, определенные в SpeciesProps в OrganismPropsValues, при этом поддерживая адекватную нормализацию?
4 ответа
Вы реализуете антипаттерн Entity-Attribute-Value. Это не может быть нормализованным дизайном базы данных, потому что он не реляционный.
Вместо этого я бы предложил шаблон проектирования наследования таблиц классов:
- Создайте одну таблицу для организмов, содержащую свойства, общие для всех видов.
Создайте одну таблицу для каждого вида, содержащую свойства, специфичные для этого вида. Каждая из этих таблиц имеет отношение "1 к 1" с организмами, но каждое свойство принадлежит своему собственному столбцу.
____________________ ____________________ | Organisms | | Species | |--------------------| |--------------------| |OrganismId (int, PK)| |SpeciesId (int, PK) | |SpeciesId (int, FK) |∞---------1|Name (varchar) | |Name (varchar) | |____________________| |____________________| 1 | | 1 ______________________ | HumanOrganism | |----------------------| |OrganismId (int, FK) | |Sex (enum) | |Race (int, FK) | |EyeColor (int, FK) | |.... | |______________________|
Это означает, что вы создадите много таблиц, но рассматривайте это как компромисс со многими практическими преимуществами для хранения свойств относительно корректным способом:
- Вы можете использовать типы данных SQL надлежащим образом, вместо того, чтобы рассматривать все в произвольной форме.
- Вы можете использовать ограничения или таблицы поиска, чтобы ограничить определенные свойства предопределенным набором значений.
- Вы можете сделать свойства обязательными (т. Е. NOT NULL) или использовать другие ограничения.
- Данные и индексы хранятся более эффективно.
- Запросы легче писать, а СУБД легче выполнять.
Подробнее об этом проекте см. Книгу Мартина Фаулера " Образцы архитектуры корпоративных приложений" или мою презентацию " Практические объектно-ориентированные модели в SQL" или мою книгу " Антипаттерны SQL: как избежать ошибок при программировании баз данных".
Хм...
Вот один из способов сделать это:
Добавьте SpeciesPropsId в таблицу SpeciesProps.
Замените PropId на SpeciesPropsId в таблице OrganismPropsValues.
Вам нужно будет немного изменить ограничения.
Необходимо добавить SpeciesProps в ограничение OrganismPropsValues.
Необходимо удалить OrganismPropsValues для ограничения реквизита.
Технически вам не нужно удалять PropId из OrganismPropsValues, но если вы сохраните его, это сделает данные избыточными.
Всякий раз, когда у вас есть подобная ромбовидная зависимость, подумайте о том, чтобы уделять больше внимания составным ПЕРВИЧНЫМ КЛЮЧАМ.
В частности, идентифицировать Организм не просто OrganismId, но комбинацией SpeciesId а также OrganismSubId (вы все еще можете иметь OrganismId, но держите его в качестве альтернативного ключа - не показывайте здесь для краткости).
Как только вы это сделаете, ваша модель может выглядеть так:
Ключевой момент, на который следует обратить внимание: SpeciesId "распространяется" вниз по обоим краям этого ромбовидного графа. Это то, что дает вам желаемое ограничение невозможности "присвоить значение" свойству, которое не было "объявлено" для данного вида.
Кстати, используйте единственное число при именовании ваших таблиц. Также рассмотрите возможность использования естественных первичных ключей (например, SpeciesName вместо SpeciesId как PK) - если все сделано правильно, это может значительно увеличить скорость ваших соединений (особенно в сочетании с кластеризацией).
Другим способом достижения этих ограничений было бы изменение PK Organism стол по капле OrganismId и добавив No, Затем сделайте ПК соединение (SpeciesId, No), Так, "Bob" было бы (Human, 1), "Rufus" было бы (Dog, 1), так далее.
Затем добавьте в OrganismPropsValues стол, SpeciesId и No (удаляя OrganismId.)
Это позволит изменить ФК с OrganismPropsValues в Props ссылаться SpeciesProps вместо:
____________________ ____________________
| Organisms | | Species |
|--------------------| |--------------------|
|SpeciesId (int, FK) | |SpeciesId (int, PK) |
|No (int) |∞---------1|Name (varchar) |
|Name (varchar) | |____________________|
|PK (SpeciedId,No) | 1
|____________________| |
1 |
| |
| |
∞ ∞
______________________ ____________________ _______________
| OrganismPropsValues | | SpeciesProps | | Props |
|----------------------| |--------------------| |---------------|
|SpeciesId (int, PK) | |PropId (int,PK,FK) | ∞-----1|PropId (int,PK)|
|No (int, PK) | |SpeciesId(int,PK,FK)| |Name (varchar) |
|PropId (int, PK) | |____________________| |_______________|
|Value (varchar) | 1
|FK (SpeciesId,No) | |
|FK (SpeciesId,PropId) | |
|______________________| |
∞ |
| |
-------------------------------