I'm attempting to design a schema in which the posts of the table aren't fixed. Ex: I've an Worker table in which the posts on the table aren't fixed and vary (characteristics of Worker aren't fixed and vary). Frequent addition of the new attribute / column is requirement.

  1. Nullable posts within the Worker table itself i.e. no normalization

  2. Rather than adding nullable posts, separate individuals posts in their individual tables ex: if Address is really a column to become added then create table Address[EmployeeId, AddressValue].

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

    Worker table

    ExtensionColumnName table

    ExtensionColumnValue table

There's a drawback may be the first couple of ways because the schema changes with every new attribute. Observe that adding a brand new attribute is frequent along with a requirement.

I don't know if this sounds like the bad or good design. If a person were built with a similar decision to create, please give a look on such things as foreign secrets / data integrity, indexing, performance, confirming etc.

It may be helpful to check out the present crop of NoSQL databases which permit you to store arbitrary teams of key-value pairs per record.

I would suggest you appear at couchdb, mongodb, lucene, and so on..

When the schema changes frequently within an SQL database this eventually ends up inside a nightmare, particularly with confirming.

Putting my way through (rowId, key, value) triads is flexible, but reduced due to the large quantity of records.

How a ERP suppliers do it is only make their schema from the fields they are certain of and give a largisch quantity of "flexfields" (i.e. 20 amounts, 20 strings, etc) in fixed named posts and employ a research table to determine what flexcolumn matches what. This enables some versatility for future years while basically getting a static schema.

I suggest using a mix of amounts two and three. Where possible, model tables for standard associations like addresses. This is actually the best approach...

However for constantly altering values that can not be made clear into logical types like this, use two tables additionally towards the EMPLOYEES table:

  • Worker_ATTRIBUTE_TYPE_CODES (two posts, employee_attribute_type_code and outline)
  • Worker_Characteristics (three posts: worker_id foreign answer to EMPLOYEES, worker_attribute_type_code foreign answer to Worker_ATTRIBUTE_TYPE_CODES, and cost)

In EMPLOYEE_ATTRIBUTES, set the main answer to be produced of:

  • worker_id
  • worker_attribute_type_code

This can stop duplicate characteristics towards the same worker.

There's a pattern, known as observation pattern.

For explanation, see these questions/solutions: one, two, three.

Generally, appears like this:

alt text

For instance, subjects worker, company and animal can all have observation Title (trait), subjects worker and animal might have observation Weight (measurement) and subject beer bottle might have findings Label (trait) and Volume (measurement). Everything matches the model.

If, while you say, new characteristics is going to be added frequently, an EAV data model may go well for you personally.