Building One-to-One tables

I faced the task of “combining” several types of objects with different properties in one table for a “global” search. I tried several solutions to this problem. Perhaps you offer something new that I could not see in the potential of SQL.

Consider the problem more specifically:
It is necessary to place in space several dissimilar objects. For example, planets, ships and portals. Such different types of objects can be dozens, not similar to others.
Basically, it is required to receive data on all these objects:

• by their coordinates in a given square.
• by ID, which should not intersect c ID of other objects.

I propose such solutions:

String parameters for objects

Create a table like this:

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 .

After that, all the fields of the object are recorded and stored, for example, in the format of JSON, XML (or simply implode ',') in the variables field.
')
Minuses:

• Working with such a structure is only possible in languages ​​that support undefined types of variables. For example, PHP.
• There is no possibility to search by fields of objects, if necessary

Pros:

• Only one request is needed to select all the necessary objects.

@Vile, 3stark , XaocCPS suggest defining the xml field in the database instead of dubious text, which linq to sql will allow you to insert an XML object in the ORM with full support for all the xml functionality you need.

Parameters of all objects in one table

The previous method, only all fields for all objects are in the same table. When adding a new parameter, a new field is added to this table.
Minuses:

• The table will take up a lot of space.

Pros:

• Only one request is needed to select all the necessary objects.
• It is possible to search for any fields of objects, if necessary
• No intersection in space by ID

Each object in its table

That is, we obtain tables with separated parameters and different starting IDs so that the search for objects by ID is possible:

- 1st table
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;

- 2nd table
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;

- 3rd table
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 .

Minuses:

• It is necessary to carry out several queries, at the intersection of the fields, in each Table, in order to obtain all the objects (for example, coordinates)
• It is necessary to monitor ID intersections between tables.
• In the case of dozens of objects, get dozens of tables

Pros:

• Data takes up as much as it should
• It is possible to search for any fields of objects, if necessary

clorz offers to use GUID or UUID functions. It returns a unique identifier. You can use it along with the usual autoincrement ID. Then do not have to follow the intersection of the latter.

Parameters of objects in a separate table
(suggested by khizhaster mcedonskiy gribunin )

When a database is represented by a table and consists of keys and parameters:

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 .

Minuses:

• More complex query processing and complexity for third-party users.
• The need to keep `value` in indexes when searching
• The more parameters in the query condition, the more difficult the query itself and the more iterations you have to do the database.

Pros:

• The most versatile structure to suit any needs.

Each parameter of objects in a separate table
(suggested by Keenn ArtemS @Vile Pilot34 )

The database is divided into tables, which when combined are combined with the help of the left join into the tables, with (null), in the absence of parameters (as in the second example, only on the fly):

- 1st table
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;

- 2nd table
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;

- 3rd table
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;

- 4th table
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 .

Minuses:

• In the case of dozens of objects, get dozens of tables
• It takes several queries to select all the necessary objects.

Pros:

• Data takes up as much as it should
• It is possible to search for any fields of objects, if necessary

And what method do you think is more optimal in such a task? (not necessarily from those proposed in Habratopik)