可扩展数据库架构-如何存储可扩展属性值 [英] Extendable Database Schema - How to store the extendable attribute values

问题描述

我们正在使用SQL Server 2008，其中一项要求是在为系统定义的实体上具有可扩展的用户定义属性。例如，我们可能有一个名为Doctor的实体，我们希望系统的管理员能够定义通常不在系统中的其他属性。这些属性很可能是链接父表或联接表的查询条件所必需的。

We are using SQL Server 2008 and one of the requirements is to have extendable user defined attributes on the entities that are defined for the system. For example, we might have a entity called Doctor, we want the admins of the system to be able to define extra attributes that normally are not in the system. These attributes will most likely be needed as query criteria linking parent or joiner tables.

会有定义属性（名称，描述，类型）等的表，但我的问题是存储实际数据值。

There will be tables that define the attributes (Name, description, type) and so forth, but my question is on the storage of the actual data values.

我不是DBA（只是假装一个程序员），但我的第一个想法是将它们存储为一个通用列，作为

Im not a DBA (just a programmer pretending to be one) but my first thought was to store them in one generic column as a

nvarchar(450)

这将涵盖大多数基本类型，并且仍然允许使用索引，但是我想我会遇到很多转换类型问题（转换为日期，数字等）以及不寻常的查询问题，因为一切都是nvarchar。

This would cover most of the basic types and still allow an index, but I thought I would run into lots of conversion type issues (converting to dates, numbers, etc.) as well as unusual query issues since everything is a nvarchar.

所以，我最近的想法是为我们将支持的每种数据类型创建一个列：

So, my latest thinking is to create a column for each data type that we would support:

ColNVarCharData
nvarchar(450) 

ColBitData
bit

ColIntData
int

。依此类推

当用户定义了可扩展属性时，他们会选择一个数据类型，然后我们ld在该列中存储该类型的属性值。例如，如果他们选择int，则在此示例中，数据值将存储在ColIntData中，而其他两列将为null。

When the user defined the extendable attribute, they would pick a data type and then we would store attribute value in that column for that type. For example, if they picked int, the data value would be stored ColIntData and the other two columns would be null in this example.

我认为这解决了转换问题，而不是将每个属性存储为通用类型。另外，我可以根据查询的使用情况为每种类型添加索引。

I think this solves the conversion issues rather than storing each attribute as a generic type. Additionally I could add indexes as needed for each type depending on the query's being used.

我倾向于使用此索引，但想知道是否还有其他建议。我已经简单地看过XML数据类型，但是模式可能会经常更改，所以我认为这是一个更好的选择。

I am leaning towards using this, but wondered if anyone else had any suggestions. I have breifly looked at the XML data type, but the "schema" could be changing quite frequently, so I thought this was a better fit.

推荐答案

此处仅是与主题相关的一些SO问题/答案。

Here are just a few SO questions/answers relevant to the topic.

• 一个


• < a href = https://stackoverflow.com/questions/4823246/how-to-model-data-with-unknown-attributes>两个


• 三个

• One
• Two
• Three

这篇关于可扩展数据库架构-如何存储可扩展属性值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！