Is it possible to implement an any-any relationship only using 2 tables?

I don't know whether my idea below is applicable:

I have 2 tables, namely A and B.

Each row in table A can be associated with zero or more rows of table B. Each row in table B can also be associated with zero or more rows of table A.

Table A contains (among others) 2 columns AId (as a primary key) and BId (as a foreign key). Table B also contains (among others) 2 columns BId (as a primary key) and AId (as a foreign key).

A cascade delete rule is also setup for each foreign key relationship in开发者_如何学Python DB and model class. It means deleting a row of A will also delete rows, associated with it, of B or deleting a row of B will delete rows, associated with it, of A.

Is it practically possible to do this scenario?

---

No, not if you are following normal form.

many to Many relationships are a hallmark of needing an intersection table.

More info:

So here's an example. question tagging. A tag can be on multiple questions, and a question can have multiple tags. this is a many to many realtionship. You COULD put multiple entries of tagIds in the Question entity's Tag Column. But you lose A LOT by doing this.

You will not have integrity, because it is VERY difficult to maintain whether or not a tag exists in your tag table as well as in the questions tag column.

This also violates normal form, because a single column cannot have multiple values.

You also cannot easily join on that column, since it has multiple values in it.

---

I'm assuming that by 'any-any' relationship you are referring to 'many-to-many'.

What you describe in your post is not a many-to-many relation. What you describe is two separate one-to-many relations.

You have a one-to-many relation from TableA to TableB via the AId column in TableB. And you have another one-to-many relation from TableB to TableA via the BId column in TableA. Having two one-to-many relationships in opposite direction is not the same thing as having a many-to-many relationship. Take Stefan's tagging example and consider three queries (QId1, QId2 and QId3) and three tags (TId1, TId2 and TId3). Try to express that all QId1, QId2 and QId3 are tagged each with all TId1, TId2 and TId3. You'll realize that you cannot, because you're trying to express 9 relations in only 6 available 'foreign key' fields. A true many-to-many relation requires up to MxN 'links' possible between two tables of size M and N, while your design allows for M+N (not surprising, since your design is M links in one of the 1-to-many relations and another N links in the other 1-to-many relation).

---

What you need is a join table. So you have an a and b table that each have a primary key. Then you create an ab table that only has 2 columns. Both are a foreign key. One goes to the a table and the other goes to the b table.

Google for "Database Normalization". You will find lots of examples.

---

It's possible, but you definitely don't want to do it that way.

You can use a comma separated string of identities in one of the tables. Looking up value from that table to the other is of course a major hassle. Looking up values from the the other table is a nightmare.

Using a cascading trigger with this method is of course out of the question. It might be possible by making an update trigger do the work, but the performance for that would be so bad that it's pointless.

To do this efficiently you absolutely need another table for the relations.