how to design a schema where the columns of a table are not fixed
Posted
by hIpPy
on Stack Overflow
See other posts from Stack Overflow
or by hIpPy
Published on 2010-05-28T20:33:13Z
Indexed on
2010/05/28
20:42 UTC
Read the original article
Hit count: 244
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).
Nullable columns in the Employee table itself i.e. no normalization
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].
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.
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.
© Stack Overflow or respective owner
