Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
8  
That Collections table is a terrible idea IMHO. How do you know in your example which ID to look for? Instead, you have to search into a semicolon-separated list, which (slightly depending on your RDBMS) will not perform well. Then how do you keep data integrity? Apart from a rather fat trigger I don't see any other possibility - that won't make insertions and updates fast. I understand that creating many junction tables don't look great at first, but it would solve the above mentioned problems immediately. –  dezso Feb 26 '13 at 10:00
3  
It is described and named in this book in chapter 2.2. Use the "Click to LOOK INSIDE" to view. –  Mikael Eriksson Feb 26 '13 at 10:08
1  
@MikaelEriksson I think that this design is actually a combination of two anti-patterns, comma-separated-columns and EAV. –  ypercube Feb 26 '13 at 13:21
    
@ypercube: Not only that, but it is two antipatterns in pursuit of a third (ORM) –  Chris Travers Mar 2 '13 at 15:12
add comment

2 Answers

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.

share|improve this answer
2  
I think the advantage is to occupy a programmer's time to make any changes so that he can get paid a lot (so long as he is not discovered) –  ETL Feb 26 '13 at 14:46
    
@ETL: Ah, but that might just trump it! Although the risk to be discovered becomes another disadvantage, and... no, I don't think I'd agree. :) –  Andriy M Feb 26 '13 at 14:49
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.