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

Question

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?

Answer 1

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.

Answer 2

On PostgreSQL I could probably actually make a design like this work, guaranteeing referential integrity, read performance, and the like. This being said I see some very significant problems here that may have evaded review. I would heartily endorse the recommendation to do something, anything, else.

The first is that your csv columns have variable arity, meaning it isn't clear at all to me what that 3-way join means, and so this is a significant problem.

Secondly, while I am pretty sure I could get referential integrity to be enforced in such a design using PostgreSQL, there are a few really nasty corner cases there which would just have to be locked out, or else one would have to resort to performance-killing tricks.

I am referring of course to what happens when you delete a part, subpart, position, etc. If you do this you have to search your collections table to check this, and that isn't good.

Now, if you are using any database which does not have the rich indexing capabilities that PostgreSQL has, you are going to run into performance issues as well. Making this work and perform adequately under read-only queries, however, would take an expert, and that's a very strong disadvantage. Indeed anyone who can make it work knows better than to try.

Your speed issues are only going to likely get worse.

tl;dr: This is a good way to use up all your time learning the internals of your database and for no practical gains. Please do everyone a favor and adopt a different solution.