Search code examples
database-designentity-attribute-value

Creating a Metatable table in a database

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?

Solution

  • 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.