Построение таблиц «Один-к-разным»

Построение таблиц «Один-к-разным»

• SQL

Передо мною встала задача — «объединить» несколько типов объектов с разными свойствами в одной таблице для «глобального» поиска. Я перепробовал несколько решений такой задачи. Возможно, вы предложите что-то новое, чего я не смог разглядеть в потенциале SQL.

Рассмотрим задачу более конкретно: 
Необходимо разместить в космосе несколько разнородных объектов. Например, планеты, корабли и порталы. Таких, различных типов объектов могут быть десятки, не похожих на других.
В основном, требуется получать данные по всем этим объектам:

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

Предлагаю такие решения:

Строковые параметры для объектов

Создать таблицу таким образом:

CREATE TABLE `space` (
 `ID` int(11) NOT NULL auto_increment,
 `x` int(11) NOT NULL default '0',
 `y` int(11) NOT NULL default '0',
 `type` char(2) NOT NULL default '',
 `variables` text NOT NULL,
 PRIMARY KEY (`ID`),
);* This source code was highlighted with Source Code Highlighter.

После чего все поля объекта записывать и хранить, например, в формате JSON, XML (или просто implode ',') в поле variables.

Минусы:

• Работа с такой струтурой возможна только в языках, поддерживающих неопределенные типы переменных. Например, PHP.
• Нет возможности поиска по полям объектов, в случае необходимости

Плюсы:

• Необходим всего один запрос для выборки всех нужных объектов

@Vile, 3stark, XaocCPS предлагают определить в базе вместо сомнительного text поле xml, которое linq to sql позволит замапить в XML-объект в ORM с полной поддержкой всего нужного вам xml-функционала.

Параметры всех объектов в одной таблице

Предыдущий метод, только все поля по всем объектам лежат в одной таблице. При добавлении нового параметра, в эту таблицу добавляется новое поле.
Минусы:

• Таблица будет занимать много места

Плюсы:

• Необходим всего один запрос для выборки всех нужных объектов
• Есть возможность поиска по любым полям объектов, в случае необходимости
• Нет пересечения в космосе по ID

Каждый объект в своей таблице

То есть, получаем таблицы с разделенными параметрами и разными стартовыми ID, чтобы поиск объектов по ID был возможен:

— 1я таблица
CREATE TABLE `ship` (
 `ID` int(11) NOT NULL auto_increment,
 `x` int(11) NOT NULL default '0',
 `y` int(11) NOT NULL default '0',
 `armor` float NOT NULL default '0',
 `maxarmor` float NOT NULL default '0',
 …
 PRIMARY KEY (`ID`),
)AUTO_INCREMENT=0;

— 2я таблица
CREATE TABLE `planet` (
 `ID` int(11) NOT NULL auto_increment,
 `x` int(11) NOT NULL default '0',
 `y` int(11) NOT NULL default '0',
 `radius` float NOT NULL default '0',
 …
 PRIMARY KEY (`ID`),
)AUTO_INCREMENT=0x10000000;

— 3я таблица
CREATE TABLE `alien` (
 `ID` int(11) NOT NULL auto_increment,
 `x` int(11) NOT NULL default '0',
 `y` int(11) NOT NULL default '0',
 `damage` float NOT NULL default '0',
 …
 PRIMARY KEY (`ID`),
)AUTO_INCREMENT=0x20000000;* This source code was highlighted with Source Code Highlighter.

Минусы:

• Необходимо осуществить несколько запросов, при пересечении полей, в каждой Таблице, для получения всех объектов (например, координаты)
• Необходимо следить за пересечениями по ID между таблицами
• В случае десятков объектов, получатся десятки таблиц

Плюсы:

• Данные занимают столько, сколько и должны
• Есть возможность поиска по любым полям объектов, в случае необходимости

clorz предлагает воспользоваться функциями GUID или UUID. Она возвращает уникальный идентификатор. Можно использовать его вместе с обычным autoincrement ID. Тогда не придется следить за пересечением последних.

Параметры объектов в отдельной таблице
(предложили khizhaster mcedonskiy gribunin)

Когда БД представляется таблицей и состоит из ключей и параметров:

CREATE TABLE `space_id` (
 `ID` int(11) NOT NULL auto_increment,
 `object_id` int(11) NOT NULL default '0',
 `parameter_name` varchar(32) NOT NULL default '',
 `value` text NOT NULL,
 PRIMARY KEY (`ID`),
);* This source code was highlighted with Source Code Highlighter.

Минусы:

• Более сложная обработка запроса и сложность разобраться сторонним людям
• Необходимость держать `value` в индексах при поиске
• Чем больше параметров в условии запроса, тем сложнее сам запрос и больше итераций приходится делать БД

Плюсы:

• Самая универсальная структура, подходящая под любые нужды

Каждый параметр объектов в отдельной таблице
(предложили Keenn ArtemS @Vile Pilot34)

БД разбита на таблицы, которые при запросе объеденяются с помощью left join в таблицы, с (null), при отсуствии параметров (как во втором примере, только на ходу):

— 1я таблица
CREATE TABLE `space` (
 `ID` int(11) NOT NULL auto_increment,
 `x` int(11) NOT NULL default '0',
 `y` int(11) NOT NULL default '0',
 `type` varchar(8) NOT NULL default '0',
 …
 PRIMARY KEY (`ID`),
) ENGINE=InnoDB, AUTO_INCREMENT=0;

— 2я таблица
CREATE TABLE `ship` (
 `ID` int(11) NOT NULL,
 `armor` float NOT NULL default '0',
 `maxarmor` float NOT NULL default '0',
 …
 FOREIGN KEY (ID) REFERENCES `space`(ID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

— 3я таблица
CREATE TABLE `planet` (
 `ID` int(11) NOT NULL,
 `radius` float NOT NULL default '0',
 …
 FOREIGN KEY (ID) REFERENCES `space`(ID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

— 4я таблица
CREATE TABLE `alien` (
 `ID` int(11) NOT NULL,
 `damage` float NOT NULL default '0',
 …
 FOREIGN KEY (ID) REFERENCES `space`(ID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;* This source code was highlighted with Source Code Highlighter.

Минусы:

• В случае десятков объектов, получатся десятки таблиц
• Необходимо несколько запросов для выборки всех нужных объектов

Плюсы:

• Данные занимают столько, сколько и должны
• Есть возможность поиска по любым полям объектов, в случае необходимости