Many-to-many without intermediate table - is it possible?

Question

I have two entities that usually have one-to-many relationship, but in rare cases should have an opportunity to be many-to-many. I don't want to join the tables with the intermediate table for every query - and i guess there are preferable patterns for "rare many-to-many", - (perhaps with additional table for m-t-m, with duplicate records or something). Any ideas?

UPD. Well, first of all i think about potential overhead with intermediate table (maybe i overestimate it), the second is about expressing real-world semantic that usually objects should have one-to-many relationship.

Answer 1

In general, you won't be able to avoid the intermediate table. Whatever you do to make the one-to-many case easier only costs you more effort to handle the general m:n case correct.

Answer 2

What's wrong with the many-to-many relationship? It's the simplest way to solve the problem, and joining the tables will be very fast if the indexes are set correctly. And don't worry about the coding time - you can factorize out the join so that you only need to write it once. If you are using Linq-like software you can store subqueries. If you are building up SQL strings by hand, you can still store the subquery as const string somewhere in your program.

However, you could avoid the extra table by creating two columns 'Primary Child' and 'Secondary Child' where primary child is NOT NULL, and Secondary Child is NULLable. If you don't care about the possiblity of multiple matches, select only primary child. In the rare cases where it does matter, select both children.

Answer 3

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

Answer 4

If something is usually a one-to-many but sometimes a many-to-many relationship, then it is only a many-to-many relationship, not a transient intermediary. What is the issue with the intermediate table -- I am assuming performance here. I have found if you use datatypes which are quick to compare (integers for example) and correct indexes, then the many-to-many model will scale quite effectively. If this is still an issue, then maybe consider revising your schema, e.g. are the many-to-many entities really the same as the usually one-to-many entities or should they be seperate tables all together?

Answer 5

One way to avoid the association table is to have each of the main tables contain something like a set of cross-referenced entries in the other table - assuming your DBMS even supports such a construct. It is infinitely less desirable for many reasons, not least of which is that it is extremely hard to either query or update the correct list automatically.

Outline schema:

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

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

Alternative mechanisms such as a non-nullable column for the ordinary cross-references (one in each table) and a nullable column for the unusual multiple cross-references (again, one in each table) still run foul of the even more unusual situation where it is not a 1:2 but a 1:3 or 1:4 relationship.

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

Answer 6

A "rare many-to-many" relationship is still a M:M relationship, and should be modeled correctly. This involves creating an intermediate or association table linking the two tables together. Your queries will be slightly more complex but only involves an additional join. But you'll have the satisfaction and admiration of your peers that you modeled your tables correctly :)

Randy

Answer 7

Well, first of all i think about potential overhead with intermediate table (maybe i overestimate it)

I think you do. Indexed joins are what databases are especially good at.

the second is about expressing real-world semantic that usually objects should have one-to-many relationship.

Well that's the question we can't answer without a more concrete example: how does the nature of the usual one-to-many relationship differ from the ‘special case’ many-to-many? Where a many-to-many occurs, is the relationship different between one of the mappings in particular, and all the rest? If so it might make sense to have a separate in-row reference for the one mapping, with a join table for the extra ones.

But if it's just the same kind of relationship — just one that usually but not always is one-to-many — then it's still a many-to-many relationship, and modelling it any other way will be making a rod for your own back. You'd end up with every query having to check the one-to-many relation first and then check the many-to-many as well, which would be more overhead, and would be a much more complicated query to write.