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.
Collectionstable 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?