MySQL: data structure for transitive relationships

Question

I tried to create a data structure for a simple and quick query (it does not matter to me to delete, insert the update speed).

Problem: transitive relations, one record may have relations through other records, the relations of which I do not want to keep separately for each possibility.

So → I know that Entry-A is associated with Entry-B, and also knows that Entry-B is associated with Entry-C, although I do not know explicitly that Entry-A is associated with Entry-C, I want to request it.

I think the solution is:

Elimination of the transitive part when inserting, deleting or updating.

Entry: id representative_id 

I would save them as sets, such as a group of records (not mysql set type, Math set, sorry if my English is incorrect). Each set will have a representative record, all installed elements will be associated with a representative element.

A new insert will insert the record and set it as itself.

If the newly inserted record should be connected to another, I simply set the representative identifier of the newly inserted record in the specified rep.id record.

Attach B to A

It does not matter. If I need to connect it to something that is not representative, it will be the same because each record in the set will have the same rep.id.

Attach C to B

Disconnect BC: The separated element will become a representative record, that is, it will refer to itself.

Disconnect BC and attach C to X

Deletion: If I delete a unique entry, it will be explanatory. But removing rep.entry is trickier. I need to select a new rep.entry for a set and set each set member rep.id to a new rep.entry rep.id.

So, remove A in this:

Result:

What do you think about this? Is this the right approach? Am I missing something? What should I improve?

Edit: Requests: So, if I want to request every record associated with a particular record whose identifier I know:

<s> SELECT * FROM entries a LEFT JOIN records b ON (a.rep_id = b.rep_id) WHERE a.id =: id >

 SELECT * FROM AlkReferencia WHERE rep_id=(SELECT rep_id FROM AlkReferencia WHERE id=:id); 

About an application that requires the following:

Basically, I store vehicle part numbers (links), one manufacturer can create several parts that can replace another, and another manufacturer can create parts that replace other parts of the manufacturer.

Reference: One OEM number for a specific product.

Cross reference: a manufacturer may create products whose purpose is to replace another product from another manufacturer.

I have to connect these links in such a way when the user is looking for a number (it does not matter what number he has), I can indicate the exact result and alternative products.

To use the example above (last image): B, D and E are different products that we can store in the store. Each of them has a manufacturer and a string name / link (I called it a number before, but it can be almost any string of characters). If I look for the reference number B, I have to return B as the exact result and D, E as an alternative.

So far so good. BUT I need to download these reference numbers. I cannot just migrate them from the ALL-IN-ONE database. In most cases, when I download links that I received from the manufacturer (somehow, most of the time from manual, but I can also use directories), I get only a list in which the manufacturer tells which other reference numbers point to it numbers.

Example:.

Filter manufacturer ASAS, filter "AS 1" has these cross-references (means replaces them):

 GOLDEN SUPER --> 1 ALFA ROMEO --> 101000603000 ALFA ROMEO --> 105000603007 ALFA ROMEO --> 1050006040 RENAULT TRUCKS (RVI) --> 122577600 RENAULT TRUCKS (RVI) --> 1225961 ALFA ROMEO --> 131559401 FRAD --> 19.36.03/10 LANDINI --> 1896000 MASSEY FERGUSON --> 1851815M1 ... 

It took years to write all the links to AS 1, but there are a lot of them (~ 1500?). And this is ONE filter. There are more than 4000 filters, and I need to store links there (and these are only filters). I think you can see, I can’t connect everything, but I should know that Alfa Romeo 101000603000 and 105000603007 are the same, even when I know (AS 1 → alfa romeo 101000603000) and (like 1 → alfa romeo 105000603007). That is why I want to organize them as sets. Each member of the set will only connect to one other member, with rep_id, which will be the representative member. And when someone wants (for example, admin, when loading these links) attach a new link to a member of the set, I just INSERT INTO References (rep_id,attached_to_originally_id,refnumber) VALUES([rep_id of the entry what I am trying to attach to],[id of the entry what I am trying to attach to], "16548752324551..");

Another thing: I do not need to worry about insertion, deletion, update frequency, because this is the task of the administrator in our system and will be rarely performed.

Answer 1

It is not clear what you are trying to do, and it is not clear that you understand how to think and design relationally. But you seem to need strings that satisfy "[id] is a member of the set named by member [rep_id]".

Stop thinking in terms of representations and pointers. Just find filling (named-) empty statements ("predicates") that say what you know about your application situations, and you can combine to ask about your application situations. Each operator receives a table (“relation”). Table columns are the names of spaces. The rows in the table are those that make his statement true. A query has an operator built from its table operators. The lines of his result are those that make his statement true. (When a query has a JOIN in table names, its ANDs statement contains table instructions. UNION ORs them. EXCEPT places AND NOT. WHERE ANDs condition. Deleting a column using SELECT matches logical EXISTS.)

Perhaps your applications are a bunch of cells with values ​​and pointers. But I suspect that your cells, pointers and connections, as well as installation and insertion, are just your way of explaining and justifying the design of your table. Your application seems to have something to do with collections or sections. If you are really trying to imagine a relationship, then you should understand that a relational table represents (is) a relationship. Regardless, you must determine what your table statements are. If you want design assistance or criticism to tell us more about your application situations, not about their presentation. All relational representations are row tables that satisfy operators.

Do you really need to call sets representative elements? If we don’t care what the name is, we usually use the "surrogate" name that is chosen by the DBMS, usually through some integer means of automatic increment. The advantage of using such a membership-independent name for a set is that we do not need to rename, in particular, by selecting an item.