четверг, 31 января 2019 г.

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

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

Передо мною встала задача — «объединить» несколько типов объектов с разными свойствами в одной таблице для «глобального» поиска. Я перепробовал несколько решений такой задачи. Возможно, вы предложите что-то новое, чего я не смог разглядеть в потенциале 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.
Минусы:
  • В случае десятков объектов, получатся десятки таблиц
  • Необходимо несколько запросов для выборки всех нужных объектов
Плюсы:
  • Данные занимают столько, сколько и должны
  • Есть возможность поиска по любым полям объектов, в случае необходимости



Автор: на
Ярлыки: