0

i'm just wondering what would be the best/most performant way to store (and later select) relations in MySQL.

Just imagine you have a table "relations", Object A of type "Window" with ID "123" and Object B of type "Frame" with ID "456" and maybe some type of clarification, what type of relation it is

so the simplest way would be to have a table with: obj_a_type, obj_a_id, obj_b_type, obj_b_id, someinfo

Having indexes on (obj_a_type, obj_a_id) and (obj_b_type, obj_b_id) would help, but with a growing table i think it will become slow

an example query would be:

SELECT ... FROM relations WHERE obj_a_type='Window' AND obj_a_id=123 OR obj_b_type='Window' AND obj_b_id=123;

This Would at least give all possible relations with the Window-Object with id 123.

Another aproach could be, to map the object-types to a set of numbers and attach them to the id. For example i expect to have a maximum of 20 different object types, i would choose a 2-digit number. I will now map the window-object to 2. now i could reduce the table to the columns "obj_a, obj_b, someinfo" and the index SHOULD be more eficient, because he just has to read 2 indexes instead of 4:

SELECT ... FROm relations WHERE obj_a=CONCAT('123' /* the id */, '02' /* Window */) OR obj_b='12302';

(the CONCAT should just show how it is being connected) This aproach should be much more performant, but it is also much more complicated to read, because you have to know, that the last 2 digits represent the type AND you should know which number stands for which type. Of course, a little helper-table that stores the possible types would be nice.

But the question now that you (hopefully unterstand what i want to do): Do you know better ways to store such data that will allow me to store relations AND will be performant even if there will be millions of records?

Also as you can see in the queries, there shouldn't be any special "direction", so the given object could be object_a or object_b.

thanks for your help!

EDIT: The reason why i have different object-types is, that they are really different, so there is no "unique object_id", thare can be a Window-Object with id 123 AND there can be a Frame-Object with id 123 at the same time

another example: i have a table persons with person_id, i have a table products with product_id and a table appointments with appointment_id they could all have relationships (a person with an appointment and vice versa, a product with an appointment, or a product related to a person, whatever)

mech
  • 617
  • 6
  • 16
  • *so the simplest way would be to have a table with: obj_a_type, obj_a_id, obj_b_type, obj_b_id, someinfo* No. Object type is a property of the object, not of the relation. The same about concatenated variant. – Akina Dec 29 '21 at 10:56
  • *thare can be a Window-Object with id 123 AND there can be a Frame-Object with id 123 at the same time* This is not correct. You may have additional column, something like "inner_number", in another columns related to the object, and have the same value for a couple of objects of different type. But the object must be identified uniquely over the whole system. *another example* In this example you have 3 separate entities. Whereas the first example tells about one entity whose instances differs in their attributes. – Akina Dec 29 '21 at 11:26
  • ok so you say a system-wide unique-id would ne the best solution in this case i would have to add a new col into all object-tables (for example articles, persons, addresses, tasks, whatever) and on every object created in one of these tables i have to create a unique-id in a separate "uniqueobject_id"-table would be a solution, just don't know what would be easier to implement (my aproach or yours, at the moment i'm not likely to insert new col into each table) but i will think about it – mech Dec 29 '21 at 14:51

1 Answers1

0

In general (no syntax):

CREATE TABLE type (type_id INT AUTO_INCREMENT PRIMARY KEY,
                   type_name VARCHAR NOT NULL,
                   another columns related to the type);
CREATE TABLE object (object_id INT AUTO_INCREMENT PRIMARY KEY,
                     object_name VARCHAR NOT NULL,
                     object_type INT NOT NULL REFERENCES type (type_id),
                     another columns related to the object);
CREATE TABLE relation (relatoin_id INT AUTO_INCREMENT PRIMARY KEY,
                       upper_obj INT NOT NULL REFERENCES object (object_id),
                       lower_obj INT NOT NULL REFERENCES object (object_id),
                       UNIQUE (upper_obj, lower_obj),
                       another columns related to the relation);
Akina
  • 39,301
  • 5
  • 14
  • 25
  • thanks you, but the reason why i have different object-types is, that they really differ example objects: articles, persons, products, whatever, they have all their own tables, i will update my question – mech Dec 29 '21 at 11:20