Entity/Attribute alternative - dynamically created tables

Question

I like pure relational design but sometimes there is a need for an Entity/Value method of storing data. Especially where the user needs to frequently create a new type of data.

I have seen in some commercial software that they dynamically create standard tables rather than use EV tables.

Obviously this isn’t a solve-all solution and can work only if you limit the sorts of data you can define. However, it has the following benefits:-

• The model is clearly defined - you don’t have to interrogate the schema to understand the schema.
• You get better performance as tables hold only the data for that type of data, hence no obviously unrelated rows need to be queried.
• Similarly, Indexes only hold values for the type of data that the table defines, again improving performance. (i.e. in the EV model, if the index is valueid,entityid - you will be searching values for entities that have nothing to do with your query - unless you partition the index).

So to me this sounds great, but run the idea past a DBA and you get a lot of sucking of teeth. Is this a good idea, what are the pitfalls, and has anyone tried to do this?

Answer 1

First of all: application should not be able to alter database structure, because they souldn't have the privileges to do so.

Second: I think the overhead of creating a good solution for creating a good database structure on the fly wouldn't pay off.

Third: It could cause some serious side effects with other things using the database (backups and maintaining scripts, other applications, etc.) when done improperly.

Answer 2

Not too long ago, I built an online survey application. I used a hybrid form as database schema:

• Store the values in a key/value table initially.
• Use dynamic, temporary tables in for they key/value pairs are transformed into a custom table per survey.
• Filling these temporary tables could be done with a single, heavy INSERT INTO temp_table SELECT ... statement, using a lot of LEFT JOINs.

This approach was ideal for this particular application, because a survey usually has a phase in which the answers are inserted, and a phase in which the results are analyzed.

• Building the temporary table could be time-consuming at times, but only had to be done once after the survey was closed.
• Querying the results table afterwards was very quick.
• Temporary tables where automatically recreated if they were deleted or out of date. They did not have to be backed up because of this.

So my advise: think about what the intended use is. This example only worked out because of the specific requirements of this application. Are you mainly inserting and updating values (OLTP)? Or do you want to run aggregate queries (OLAP)? Build your schema according to the answer on this question.