Design help for searching objects with variable structure
I've recently come across a problem that I'm having some difficulty coming up with an elegant solution for. Here is a generic description of the problem: my company tests widgets and I have an application which tracks all of the tests that need to be performed and which ones have been completed. There is one main table that contains the common attributes for every test: A,B,C, and D. Each test can have a user defined set of attributes. For example, one group of tests could have additional attributes E,F,G,H and another could have I,J,E, another K,L,M, another X,K,Z,Y,E (there can be overlap) and so on. Right now, I store all of the user defined attributes that are common to each test in separate tables (I call them classes) and I have a catalog of the classes that stores the table name, attribute names, etc. The problem I have is searching through all of the custom attributes. I've been asked to add a feature to the application where someone could search the tests and see what can be performed, like: if A = 50 and K = green, which tests can be performed? Not all of the attributes will have A or K and then they might be named differently. One use might call K "color" and another "colour" or "clr". The only way I know how to effectively search would be to move the attribute to the common table but then that has several drawbacks (wasted table space, loss of flexibility). I'm sure this is not a new problem but I haven't been able to come up with a viable solution nor find help on the web. How have other people solved a problem like this? Essentially, I'm looking for the best way to search objects with a variable structure. I could also handle it in the front end but that would be really slow. I'd rather craft a search query and let the database give me the resutls. Thanks.