keeping database columns consistent with documentation

Question

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?

Answer 1

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.