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?