如何设计一个表的列不固定的模式 [英] how to design a schema where the columns of a table are not fixed

问题描述

我想设计一个表格的列不固定的模式。例如：我有一个Employee表，其中表的列不是固定的和变化的（Employee的属性不是固定的和变化的）。

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. Employee表本身的空列，即没有规范化

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

不要添加可空列，而是在其各个表中分隔这些列ex：if Address是要添加的列，然后创建表Address [EmployeeId，AddressValue]。

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].

创建表ExtensionColumnName [EmployeeId，ColumnName]和ExtensionColumnValue [EmployeeId，ColumnValue]。 ExtensionColumnName将ColumnName作为Address，ExtensionColumnValue将ColumnValue作为地址值。

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

员工表

EmployeeId

名称

Employee table
EmployeeId
Name

ExtensionColumnName表

ColumnNameId

EmployeeId

ColumnName

ExtensionColumnName table
ColumnNameId
EmployeeId
ColumnName

ExtensionColumnValue表

EmployeeId < br>
ColumnNameId

ColumnValue

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.

推荐答案

我建议使用数字2和3的组合。在可能的情况下，标准关联（如地址）的模型表。这是最理想的方法...

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

但是对于不能总结成这样的逻辑分组的不断变化的值，除了 EMPLOYEES 表：

But for constantly changing values that can't be summarized into logical groupings like that, use two tables in addition to the EMPLOYEES table:

• EMPLOYEE_ATTRIBUTE_TYPE_CODES（两列， employee_attribute_type_code 和DESCRIPTION）


• EMPLOYEE_ATTRIBUTES（三列：employee_id外键为EMPLOYEES，employee_attribute_type_code外键为EMPLOYEE_ATTRIBUTE_TYPE_CODES和VALUE）

在 EMPLOYEE_ATTRIBUTES 中，设置主键为：

• employee_id


• employee_attribute_type_code

给同一个雇员。

这篇关于如何设计一个表的列不固定的模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！