Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For a project, the client wanted to be able to add fields to any item and have them be searchable. To make them searchable, that would require the fields to take a column, but that would make the database design, in documentation, inconsistent with the columns present in the database.

So we ended up implementing a virtual column system; this made is so that 4 tables were needed for this feature:

  • main table containing the basic columns
  • a category table
  • a table to hold the virtual column information
  • a table used to define what the virtual column was called and what it holds.

When a column is added to a category, any row in with that category has an entry made in the virtual column table, which is foreign linked to the column type and the row it was made for.

Is this a bad idea for design or performance?

share|improve this question
2  
This is basically a hybrid EAV system. And it's bad for performance. –  JNK Jan 6 '12 at 16:09
    
Personally the part that worries me is having non-technical people add fields to entities. Accidental denormalisation, anyone? –  Simon Righarts Jan 8 '12 at 23:02
add comment

1 Answer

If the only reason not to add columns is "but that would make the database design, in documentation, inconsistent with the columns present in the database" then you need to look at your procedures.

If there is a business reason to add the fields and your systems support it then you need to have your documentation updated at the same time as the table. This should not be much more than adding a description fo the field to the documentation, perhaps having a place in documentation just for these user added fields.

Note that the business wants the system and the documentation is only a tool to help and not the end result.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.