您将如何在SQL数据库中创建和存储用户定义的自定义字段？

I need to allow users to add new fields to a record, e.g. if there is a Contact record, a user may want to add a "SSN" numeric field and a "Birthdate" date/calendar field. They would do this through the UI of course.

我需要允许用户在记录中添加新字段，例如如果存在联系人记录，则用户可能想要添加“SSN”数字字段和“出生日期”日期/日历字段。他们当然会通过用户界面做到这一点。

Those fields should then be available for entry for all contact records.

然后，这些字段应可用于所有联系人记录的输入。

Considering that my application runs for many users concurrently (not a single-company deployment etc.) and theoretically everyone could add their own custom fields, what would be the best practice to store this information in a database, especially when it needs to be searchable?

考虑到我的应用程序同时为许多用户运行（不是单个公司部署等），理论上每个人都可以添加自己的自定义字段，将这些信息存储在数据库中的最佳做法是什么，特别是当需要搜索时？

3 个解决方案

#1

We add almost in our all application/products additional attribute/field support for given flexibility to user
Like we have a product category, In the category, customer can define additional attribute of any product
what we are doing in the DB level is:
Category Table have some additional column like: Text1Att, Text2Att...for text value support, Num1Att, Num2Att... for Number value support, Date1Att, Date2Att... for datetime value support, ID1Att, ID2Att... support for ID from other table like you can add dropdown, listbox,...
here all the column have String datatype.
what we store here is

we will store meta information here, like for Text1Att meta is
SSN;textbox;50;true;false;Null;
Caption of field;Control Type;Max length;is Required field;is Custom validation required; Custom Validation message;
birth place;textbox;100;true;true;Invalid Value;
Same for Numeric field ...
for date meta information will look like
birth date;Calendar control;true;true;Invalid Date;
Caption of field; Calendar control or can be other;is required;is Custom Validation; Custom Validation message;

What are doing in product table is add same number of column and have datatype text1Att,.. is varchar, num1Att have numeric, date1Att have datetime, ID1Att have int

我们几乎在我们的所有应用程序/产品中添加了额外的属性/字段支持，以便为用户提供灵活性就像我们有一个产品类别一样，在类别中，客户可以定义任何产品的其他属性我们在DB级别所做的是：Category Table有一些额外的列，如：Text1Att，Text2Att ...用于文本值支持，Num1Att，Num2Att ...用于数值支持，Date1Att，Date2Att ...用于日期时间值支持，ID1Att，ID2Att ...支持来自其他的ID像你可以添加下拉列表，列表框，...这里所有列都有String数据类型。我们在这里存储的是我们将在这里存储元信息，就像Text1Att meta是SSN; textbox; 50; true; false; Null;字幕;控制类型;最大长度;是必填字段;是否需要自定义验证;自定义验证消息;出生地;文本框; 100;真;真;无效价值;对于数字字段相同...对于日期元信息将看起来像出生日期;日历控件; true; true;无效日期;字幕;日历控件或可以是其他;是必需的;是自定义验证;自定义验证消息;在产品表中做的是添加相同数量的列并且具有数据类型text1Att，..是varchar，num1Att有数字，date1Att有datetime，ID1Att有int

What we are doing GUI side is : In category definition page add these attribute and build meta information at runtime and store in category table
On the other hand when we define product in category, meta information will be read and traverse from category table and populate in product definition page like other fields.

我们在GUI方面做的是：在类别定义页面中添加这些属性并在运行时构建元信息并存储在类别表中另一方面，当我们在类别中定义产品时，将从类别表中读取和遍历元信息并填入产品定义页面与其他领域一样。

if u need further help, I can provide you images so that you will better understand how can be done this.
we are experience and analyze, this is much flexible approach

如果您需要进一步的帮助，我可以为您提供图像，以便您更好地了解如何做到这一点。我们是经验和分析，这是非常灵活的方法

#2

Have a table that stores the field names and types.

有一个存储字段名称和类型的表。

field_ID     INT
field_name   VARCHAR
field_type   ENUM('int','float','text','richtext')

Have a table that stores a link to an entry in the record table, a link to an entry in the field table, and the field value.

有一个表，用于存储记录表中条目的链接，字段表中条目的链接以及字段值。

fieldvalue_fieldID   INT
fieldvalue_recordID  INT
fieldvalue_value     BLOB

Making it searchable is another challenge - you would need to grab any searchable content out of that fieldvalue_value and index that. This would be database-specific. In MySQL you could make that a TEXT value and add a MySQL FULLTEXT index on it.

使其可搜索是另一项挑战 - 您需要从该fieldvalue_value中获取任何可搜索的内容并将其编入索引。这将是特定于数据库的。在MySQL中，您可以将其设为TEXT值并在其上添加MySQL FULLTEXT索引。

#3

Your best options are:

您最好的选择是：

Allow the user to alter their own database schema, perhaps by uploading a module or running a script.

允许用户更改自己的数据库模式，可能是通过上载模块或运行脚本。
Use an XML field, and a database that supports indexes and querying on the contents of that field

使用XML字段以及支持索引和查询该字段内容的数据库

These are recommended by Martin Fowler, here: http://martinfowler.com/bliki/UserDefinedField.html

这些是Martin Fowler推荐的，这里是：http：//martinfowler.com/bliki/UserDefinedField.html

#1