Modelling Entities That Are Unknown At Design Time

Hi Everyone,

I'm attempting to design a schema for a project I working on. The idea is that I can store a list of documents and associate meta data with them.

The problem is that I don't know what that meta data will be at design time. The user will create the meta data fields in the software. For the first design I just had a table called meta field that contained the meta field defintion, and another table called itemdata that contained the document primary key, the metafield primary key and a value encoded as a varchar. This works fine while the list of documents is small.

Another idea I've thought of is to have seperate tables for all of the metafields. This would allow me to be very specific about the values that can be stored in that column etc. It also turns out that for the kind of queries I'm doing its about 4 times as fast. The only problem is that the software will need to create these tables at run time.

What I would like to know is, is this a really bad idea? If so why?. Also are there any other ways I can store this kind of data?

The kind of queries I am doing are find all the documents where metafield 1 is A and metafield 2 is B and metafield 3 is C and metafield 4 is D etc.

Cheers,
Mark