Creating a Metatable table in a database

Question

Does anyone have any suggestions for creating meta tables in a database? These tables would be used to emulate tables in a database, using a database. This is for when you want to easily add structure (more fields) to a database on the fly without having to worry about all the technicalities involved. The only example I have looks something like this:

Table: MetaTables Fields: tableName, tableDescription

Table: MetaFields Fields: tableName, fieldName, fieldDesc, fieldDesc

Table: MetaCodes Fields: tableName, fieldName, codeName, codeValue, etc...

I've never really used anything like this before and was wondering if there are any "gotchas" to look out for.

Is something like this reasonably maintainable or would you advise against it?

Answer 1

What you are talking about is a flexible schema model often referred to as an Open Schema model or Entity-attribute-value model. Google those and you will gets lots of references and articles.

I would also suggest you not shy away from a design wherein you add columns. Too often I see people take an approach like this when the level of volatility in the design is just not that significant. Scripting out the creation of new data containers (tables,columns etc) is not that hard.

Answer 2

As keithwarren7 suggested, do some Google-ing, and you will see (as per your comment) that it is totally NOT cool to do this. (Ok, it is in some cases, but almost always not, IMO.)

What is cool, and much more rare than you would think, is using relational databases in the way they were intended to be used, with a well-thought-out schema design. You will find this gives you better data integrity, performance, uses less storage, and is generally a whole lot easier to work with than the Entity-Attribute-Value model is. There are some clear pitfalls you will read about, e.g., you will be responsible for enforcing referential integrity yourself, rather than the db.

At any rate, research this idea carefully (beyond stackoverflow) before you go ahead with it, as it is a really big deal to change your mind once you have implemented this way.

Answer 3

Instead of using tables and fields to express a dynamic structure, you could always use a single field of XML data. Many mainstream RDBMS's permit indexing attributes/elements within the XML, so your queries may even be performant.

Answer 4

I think that the idea of taking a fully-implemented relational database, ignoring almost all of the features, and building your own relational database on top of the relational database is trouble waiting to happen.

Building your own model sounds "cool".

You'll of course, have to build your own meta-level space management. That is a moderate pain in the neck.

Query exection, optimization, handling of indexes and what-not is relatively complex.

Then, there are transactions, locking, deadlock detection and the like. This is really hard to get correct. But, once you get it to work, it will have a huge "cool" factor.

Also, you'll need to invent an API. ODBC works at a low level, and you want to work at a higher level, so you'll have to invent your own "cool" version of ODBC.

The "cool" factor had best trump all of this work. You'll be spending years to get this to work.

Answer 5

If your boss wants to be really cool, and wants dynamic metadata, forget about clunky old relational databases -- try RDF and semantic data engines! RDF allows you to store and query metadata and data identically. Every entity in your database is completely dynamic and self-describing. See Sesame for an example implementation.

RDF is the logical extension of an EAV design.

Answer 6

Mostly a meta-table schema defeats the purpose of using a relational database in the first place. Aside from the basic transaction/ACID properties, a relational database is most useful in adding two big features to your system: a) sharing the data with applications like report generators, and b) allowing for ad-hoc queries.

Some other data-persistence solution might be better if neither of the above two apply and you still want to be generic. You can roll your own solution in something like Berkley DB quite elegantly or try a variation on a generic schema:

http://theprogrammersparadox.blogspot.com/2008/06/structuring-noun-verb-data.html

Paul.

Answer 7

I agree with the comments that say that E-A-V looks cools, but is not cool. It's doing a whole lot of work that turns out to be submarginal or outright counterproductive.

Where I have used my own user tables containing metadata is for purposes of comparing two different databases to find out where they diverged. Comparing values between two identically structured schemas is straightforward. Detecting differences between schemas is more subtle, and involves comparing metadata.

My approach was to copy data from the metadata tables of two or more databases into the user defined metadata tables in my "metadatabase", and then proceed to compare. In about an hour, I found a column that had been defined as real in one database and integer in the other. This had been hiding from DBAs for months. I also found several columns that were missing from one or the other of the databases.

Today, there are tools that will do this kind of comparison for you. But it's really not hard to roll your own, if you understand the schema of the system tables your DBMS builds for you.

Answer 8

Sorry, it really is, as other posters point out, reinventing the wheel.

If you want to use this data, most databases have tables containing the database schema. This can be used for the purposes of reflection.

Any good database manual should tell you how to access these tables.

Answer 9

Do some performance tests with a large dataset in one large eav-table. If your boss sees a slow performance he/she wont be using the word 'cool'.