动态在数据库表中添加列的设计模式 [英] Design Pattern to add columns in database table dynamically

问题描述

用户希望在UI中动态添加新字段。这个新字段应该存储在数据库中，并且应该允许它们对它执行CRUD。

The user wants to add new fields in UI dynamically. This new field should get stored in database and they should be allowed to perform CRUD on it.

现在，我可以通过指定XML来做到这一点，但我希望有一个更好的方法这些新列是可搜索的。另外，触发 ALTER 语句并添加新列的想法似乎是错误的。

Now I can do this by specifying a XML but I wanted a better way where these new columns are searchable. Also the idea of firing ALTER statement and adding a new column seems wrong.

有人可以帮我设计吗？如何解决此问题的数据库服务器端模式？

Can anyone help me with a design pattern on database server side of how to solve this problem?

推荐答案

可以使用键值系统来解决。您将创建一个表，该表具有要注释的表的主键列，用于属性名称的列以及用于其值的列。当用户要向人员123的记录添加属性（例如身高）时，可以在新表中添加带有值（123， HEIGHT， 140.5）的行。

This can be approached using a key value system. You create a table with the primary key column(s) of the table you want to annotate, a column for the name of the attribute, and a column for its value. When you user wants to add an attribute (say height) to the record of person 123 you add a row to the new table with the values (123, 'HEIGHT', '140.5').

通常，将值转换为TEXT进行存储，但是如果您知道所有属性都是数字，则可以为value列选择其他类型。您也可以（不建议）根据数据类型使用几个不同的值列。

In general you cast the values to TEXT for storage but if you know all the attributes will be numeric you can choose a different type for the value column. You can also (not recommended) use several different value columns depending on the type of the data.

此技术的优点是您无需修改​​数据库用于添加新属性的结构，并且仅为具有属性的记录存储属性。缺点是查询不像所有列都在主数据表中那样简单。

This technique has the advantage that you don't need to modify the database structure to add new attributes and attributes are only stored for those records that have them. The disadvantage is that querying is not as straightforward as if the columns were all in the main data table.

这篇关于动态在数据库表中添加列的设计模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！