Many-to-many without an intermediate table - is this possible?

Question

I have two objects that usually have a one-to-many relationship, but in rare cases should be able to be many-to-many. I don't want to join tables with a staging table for each query - and I think there are preferred templates for "rare many-to-many" - (maybe with an extra table for mtm, with duplicate records or something else) . Any ideas?

UPD. Well, first of all, I think about potential overhead with an intermediate table (maybe I overestimate it), the second - about the expression of real semantics, usually objects should have a one-to-many relationship.

Answer 1

The Rare Many-to-Many relationship is still an M: M ratio and needs to be modeled correctly. This involves creating an intermediate or associative table linking the two tables together. Your queries will be a little more complex, but will only include an additional connection. But you will be satisfied and delighted with your peers that you correctly modeled your tables :)

Randy

Answer 2

What happened to many-to-many relationships? This is the easiest way to solve the problem, and joining tables will be very fast if the indexes are set correctly. And don't worry about coding time - you can decompose the connection, so you only need to write it once. If you use Linq software, you can store subqueries. If you create SQL strings manually, you can still store the subquery as a const string somewhere in your program.

However, you can avoid the extra table by creating an “Initial Child” of two columns and a “Secondary Child”, where the primary child is NOT NULL and the Secondary Child is NULLable. If you don’t care about the possibility of multiple matches, select only the primary child. In rare cases when it matters, choose both children.

Answer 3

Well, first of all, I’m thinking about potential overhead with an interim table (maybe I overestimate it)

I think yes. Indexed joins are which databases are especially good.

the second is the expression of real semantics, since usually objects should have a one-to-many relationship.

It’s good that the question we cannot answer without a more concrete example is: how does the nature of the usual one-to-many relationship differ from the special case of many-to-many? Where does many-to-many occur, is the relationship between one of the mappings, in particular, and everything else? If so, it makes sense to have a separate link in the row for one mapping to the join table for additional ones.

But if this is only the same relationship - only one that is usually, but not always a one-to-many relationship, then it is still a many-to-many relationship, and modeling it in some other way will create a rod for your back . As a result, each request must first check the one-to-many relationship, and then check the many-to-many relationship, which would be more overhead, and would be a much more complex write request.

Answer 4

I am going to suggest that your question is about relational (SQL, for example) databases. If you want to model a table in "normal" form, then an intermediate table is required. But without limiting the normality, you can model your case using one table with m * n rows (the result of an internal join, if you have table A, intermediate table and table B. This can be useful when storing data, but I would not suggest using this strategy if the table often deleted, updated, or inserted rows.

Answer 5

In general, you cannot escape the staging table. Whatever you do to simplify the one-to-many business, you need more effort to deal with the general rule m: n.

Answer 6

If something is usually a one-to-many relationship, but sometimes many-to-many relationships, then this is a many-to-many relationship, not a temporary intermediary. What is the problem with the staging table - here I take on the performance. I found that if you use data types that compare quickly (for example, integers) and the correct indexes, then the many-to-many model will scale quite efficiently. If this is still a problem, then perhaps consider revising your circuit, for example. are many-to-many objects, essentially the same as usual for one-to-many objects, or should they be separate tables together?

Answer 7

One way to avoid the association table is to have each of the main tables contain something like a set of records with cross-references in another table - if your DBMS even supports this design. This is infinitely less desirable for many reasons, not least because it is extremely difficult to either request or update the correct list automatically.

Circuit diagram:

create table t1 (id integer, xref_t2 set(integer), ...other columns...); create table t2 (id integer, xref_t1 set(integer), ...other columns...); 

Note: there is no easy way to declare a referential integrity constraint to ensure that the values ​​in 'xref_t2' are indeed still valid (or write a trigger to enforce the constraint).

Alternative mechanisms, such as a column with a non-zero value for regular cross-references (one in each table) and a column with a zero value for unusual multiple cross-references (again, one in each table), are still hushed up even more unusual situation when it not a 1: 2 ratio, but a 1: 3 or 1: 4 ratio.

The best way to do this is with an explicit association table.