What are the advantages of a database design with single table for MtM relationships

database-designorm

The database that backs our software product has a table design like the following:

Positions
ID    Name    Parts
1     One     12345
2     Two     12346
3     Three   12347

Collections
ID      TableID  Collection
12345   1;1      1;2
12346   1;1      3;4
12347   1;2;2    5;1;2

Parts
ID     Name
1      TestOne
2      TestTwo
3      TestThree
4      TestFour
5      TestFive

SubParts
1      SubPartOne
2      SubPartOne

From the above example, each Position has a collection of Parts, but these are mapped generically (without foreign key constraints) into the Collections table. The Collections table keeps track of all relationships between all objects, not just between the example tables shown above, and will be used any time a collection is used.

That means that if I want to get the Position with ID 1, and all of its parts. I have to do three queries:

SELECT * FROM Positions WHERE ID = 1
SELECT * FROM Collections WHERE ID = 12345

Split the string by the semicolons

SELECT * FROM Parts WHERE ID = 1 OR ID = 2

The advantages of the current approach are:

  • A collection of something can actually be selected from more than table. For example, if SubPart inherits from Part, and position contains a list of Parts, this will be handled OK.

The disadvantages of the current approach are:

  • Speed? We need to do many queries to load data.

  • The Collections table is the largest or second largest table in our database.

  • No support from ORM frameworks? We are looking at switching our persistency layer to use EF or NHibernate etc. but I don't believe our current design will be supported by any of these frameworks.

My questions are:

  • Is there advantages or disadvantages to this approach that I haven't listed?

  • Is this design familiar to people and if so, does it have a name?

  • If the design is familiar, is it supported by out of the box ORM frameworks?

Best Answer

I don't know if this design has a name, but it is:

  • horrible to retrieve data with,

  • horrible to modify,

  • impossible to guarantee referential integrity.

Can't think of any advantage, but if I could, I'm sure none would trump these three.

Multiple many-to-many tables would be much better compared to this design, although I won't insist that would be the only alternative.