sql - how to design a schema where the columns of a table are not fixed

Question

I am trying to design a schema where the columns of a table are not fixed. Ex: I have an Employee table where the columns of the table are not fixed and vary (attributes of Employee are not fixed and vary). Frequent addition of a new attribute / column is requirement.

1. Nullable columns in the Employee table itself i.e. no normalization

2. Instead of adding nullable columns, separate those columns out in their individual tables ex: if Address is a column to be added then create table Address[EmployeeId, AddressValue].

3. Create tables ExtensionColumnName [EmployeeId, ColumnName] and ExtensionColumnValue [EmployeeId, ColumnValue]. ExtensionColumnName would have ColumnName as "Address" and ExtensionColumnValue would have ColumnValue as address value.

   Employee table
   EmployeeId
   Name

   ExtensionColumnName table
   ColumnNameId
   EmployeeId
   ColumnName

   ExtensionColumnValue table
   EmployeeId
   ColumnNameId
   ColumnValue

There is a drawback is the first two ways as the schema changes with every new attribute. Note that adding a new attribute is frequent and a requirement.

I am not sure if this is the good or bad design. If someone had a similar decision to make, please give an insight on things like foreign keys / data integrity, indexing, performance, reporting etc.

Answer 1

It might be useful to look at the current crop of NoSQL databases which allow you to store arbitrary sets of key-value pairs per record.

I would recommend you look at couchdb, mongodb, lucene, etc ...

If the schema changes often in an SQL database this ends up in a nightmare, especially with reporting.

Putting everything in (rowId, key, value) triads is flexible, but slower because of the huge number of records.

The way the ERP vendors do it is just make their schema of the fields they're sure of and add a largisch number of "flexfields" (i.e. 20 numbers, 20 strings, etc) in fixed named columns and use a lookup table to see which flexcolumn corresponds to what. This allows some flexibility for the future while essentially having a static schema.