I'm currently busy making a Python ORM which gets all of its information from a RDBMS via introspection (I would go with XRecord if I was happy with it in other respects) — meaning, the end-user only tells which tables/views to look at, and the ORM does everything else automatically (if it makes you actually write something and you're not looking for weird things and dangerous adventures, it's a bug).
The major part of that is detecting relationships, provided that the database has all relevant constraints in place and you have no naming conventions at all — I want to be able to have this ORM work with a database made by any crazy DBA which has his own views on what the columns and tables should be named like. And I'm stuck at many-to-many relationships.
First, there can be compound keys. Then, there can be MTM relationships with three or more tables. Then, a MTM intermediary table might have its own data apart from keys — some data common to all tables it ties together.
What I want is a method to programmatically detect that a table X is an intermediary table tying tables A and B, and that any non-key data it has must belong to both A and B (and if I change a common attribute from within A, it should affect the same attribute in B). Are there common algorithms to do that? Or at least to make guesses which are right in 80% of the cases (provided the DBA is sane)?
So far, I see the only one technique covering more than two tables in relation. A table X is assumed related to table Y, if and only if X is referenced to Y no more than one table away. That is:
"Zero tables away" means X contains the foreign key to Y. No big deal, that's how we detect many-to-ones.
"One table away" means there is a table Z which itself has a foreign key referencing table X (these are easy to find), and a foreign key referencing table Y.
This reduces the scope of traits to look for a lot (we don't have to care if the intermediary table has any other attributes), and it covers any number of tables tied together in a MTM relation.
If there are some interesting links or other methods, I'm willing to hear them.