How to create a schema in which table columns are not fixed

Question

I am trying to create a schema in which table columns are not fixed. Example: I have an Employee table where the table columns are not fixed and are changing (Employee attributes are not fixed and are changing). Often adding a new attribute / column is a requirement.

• Impossible columns in the Employee table itself, i.e. normalization

• Instead of adding nullable columns, separate these columns in your separate ex tables: if Address is the column you want to add, then create the Address [EmployeeId, AddressValue] table.

• Create tables ExtensionColumnName [EmployeeId, ColumnName] and ExtensionColumnValue [EmployeeId, ColumnValue]. ExtensionColumnName will have a ColumnName value as "Address", and an ExtensionColumnValue will have a ColumnValue value as an address value.

  Employee table
  EmployeeID
  Name

  ExtensionColumnName table
  ColumnNameId
  EmployeeID
  Columnname

  ExtensionColumnValue Extension Table
  EmployeeID
  ColumnNameId
  Columnvalue

There is a drawback - these are the first two ways to change the schema with each new attribute. Please note that adding a new attribute is frequent and requires.

I'm not sure if this is a good or bad design. If someone had a similar solution, please give an idea of ​​such things as foreign keys / data integrity, indexing, performance, reporting, etc.

Answer 1

I recommend using a combination of two and three. Where possible, model tables for standard associations such as addresses. This is the most ideal approach ...

But to constantly change values ​​that cannot be reduced to such logical groupings, use two tables in addition to the EMPLOYEES table:

• EMPLOYEE_ATTRIBUTE_TYPE_CODES (two columns, employee_attribute_type_code and DESCRIPTION)
• EMPLOYEE_ATTRIBUTES (three columns: employee_id foreign key for EMPLOYEES, employee_attribute_type_code foreign_dependent key for EMPLOYEE_ATTRIBUTE_TYPE_CODES and VALUE)

In EMPLOYEE_ATTRIBUTES set the primary key:

• employee_id
• employee_attribute_type_code

This will stop duplicate attributes for the same employee.

Answer 2

It might be useful to look at the current assembly of NoSQL databases, which will allow you to store arbitrary sets of key-value pairs for each record.

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

If the schema changes frequently in the SQL database, it ends in a nightmare, especially with reporting.

Nesting everything in a (rowId, key, value) triad is flexible, but slower due to the sheer number of entries.

The way ERP providers do this, simply makes their own field map of which they are sure, and adds a large number of "flexfields" (ie 20 numbers, 20 rows, etc.) in fixed named columns and uses a search to see which flexcolumn matches what. This allows for future flexibility while still having a static circuit.

Answer 3

There is a pattern called an observation pattern.

For clarification, see these questions / answers: one , two , three .

In general, it looks like this:

For example, subjects, workers, companies, and animals may have surveillance. The name (attribute), the subject of the employee and the animal may have an observation. Weight (measurement) and a subject bottle of beer may have observations. Label (sign) and volume (dimension). All this fits into the model.

Answer 4

If, as you say, new attributes are added frequently, the EAV data model may work well for you.

Answer 5

Merge ExtensionColumn tables into one

 Property: EmployeeID foreign key PropertyName string PropertyValue string 

If you use a monotone sequence to assign primary keys in all your object tables, then properties for all objects can be stored in one property table.

Answer 6

I would use a combination of 1 and 2. If you often add attributes, I don't think you have a data requirements descriptor.

I see that some added attributes belong to another table. If you keep adding attributes like certified Java, asp certified ..., you need a certification table. This may be relevant to the certification code table, which lists the available certificates.

Attributes, such as a manager, can be either an attribute or a relationship table. If you have several relationships between employees, then consider the relationship table with the type of release. Organizations with a matrix management structure will require a distribution table.

Addresses and phone numbers often go in separate tables. An address key, such as employee_id, address_type, would be appropriate. If you need a story, add a start_date column to the key.

If you are saving history, I recommend using the start_date and end_date columns in the respective columns. I am trying to use relationships in which the record is active when 'start_date <= date-being-Consider <end_date' is true. Attributes such as weight, eye color, etc.