查找带有动态列的配置文件表的Server表设计正确性检查

Question

对于系统中的两个不同的配置文件，在我的例子中，教师和学生，推广一个概要表是否有意义？我正在做这件事，只想对我的设计方法进行一次全面的理智检查。对此表示赞赏。背景如下：

我们正在建立一个由教师和学生组成的网络系统。两个人都在系统里有账户。两者都有系统中的配置文件。

我的问题是关于那些配置文件表的表格设计。

对于与其相关的元数据，教师配置文件是相当静态的。每位教师都有一定数量的字段来公开有关该个人的信息(学校、学位等)。然而，学生们的情况就不同了。我们正在使用一个windows服务从源源不断的excel电子表格中提取有关学生的各种数据。

数据被移动到我们的数据库中，然后这些字段将与学生的个人资料相关联。因此，每个学生的个人资料中可能都有非常不同的字段。

我最初从三个表的概念开始：

帐户

AccountID

TeacherProfiles

TeacherProfileID
AccountID
SecondarySchool
University
YearsTeaching
Etc...

StudentProfiles

StudentProfileID
AccountID
Header
Value

StudentProfiles表将保存来自Excel电子表格的列标题的名称和相关的值。

我已经发展了一些设计，以更一般地处理配置文件所附的ERD图像。教师和学生的“标题”存储在一个名为ProfileAttributeTypes的表中，并将响应(来自excel文档或通过web表单上的输入字段)放入ProfileAttributes表中。这样，学生和教师配置文件都可以与配置文件字段的动态流相关联。“权限”表告诉我们是在处理学生还是教师。

由于这个系统可能发展很快，我想确保基础是坚实的。你能不能提供关于这个设计的反馈，让我知道它是否合理，或者你是否能看到它可能造成的问题，如果是的话，有什么更好的方法呢？

提前谢谢。

​

​

Answer 1

财产袋法

您建议的数据模型依赖于一个“属性包”(配置文件的键值项集合)。该模型的优雅之处在于，您可以扩展您的属性，而无需进行数据模型更改。

缺点是，您将经常不得不“枢轴”数据，您的表(和索引)将很快爆炸。(我的经验: 50K记录中每个键有200个属性=1,000万个属性，无需跟踪属性上的任何更改。)

如果您主要需要查询一个特定属性以获取密钥，则可以推荐此模型。想一想“有多少人有数学学位？”数学学位是财产钥匙的地方。

Xml字段方法

通过这种策略，我们向Profiles表中添加了一个" xml“字段，该表以xml的形式接受属性列表。该模型还允许您扩展属性的数量，而不必进行数据模型更改。

Sql Server对这些字段(通过xpath查询、xml索引等)提供了非常好的支持，当然，它的好处是您可以保持一个简单的数据模型，它允许您在xml字段中存储您喜欢的任何内容。

当字段内容作为一个整体被替换时，建议使用这个模型，您可以通过xpath查询修改xml字段中的数据，但是它非常慢。

稀疏柱

稀疏柱系统是在Server 2008中引入的，它允许您在不密集的表中创建许多不同的字段。这样做的好处是，它允许您创建比1024限制更多的列，并且当未填充字段时，非填充字段将不会占用空间。

缺点是您需要预先了解所有可能的字段，否则每次遇到新字段时都会查看数据模型的更改。如果表中大部分为空列，则此模型是很好的。

该采取哪种方法？

这是最困难的部分，这取决于你想要对数据做什么。根据我的经验，属性包方法可以很好地处理小数据集，如果您不需要跟踪属性的更改。(我见过一个月后有超过10亿次记录的情况)

当您经常需要查询字段的特定内容时，Xml字段可能是一个巨大的痛苦，但它可以很好地存储只需要“按键”的信息。

当填充不到30%-40%的记录时，稀疏列工作良好。

附加备注：在您的数据模型中存储诸如“多年教学”之类的东西被认为是一种不好的做法，因为您必须随时更新该值。最好是储存“教学开始年”，并计算出增量值。

Answer 2

我不认为你的设计很好。该模型混合了用户和实体的概念。

这里是一个更合适的设计的开始。

t_User

t_User_Settings (Profile)

t_Permissions

t_Actions

t_Student

t_Teacher

t_Student_Attributes

t_Teacher_attributes

与用户相关的项/属性属于t_User或t_User_Settings域相关项/属性属于t_t/t_t_Attribute或t_t_User/t_Student_Attribute

您可以通过外键将域概念(教师/学生)与用户概念关联起来。或者您可以创建一个t_Teacher_User + t_Student_User表。

请注意，通过读取表名，您就可以准确地知道到哪里去了。

Answer 3

根据我的经验，检查数据模型的最佳方法是计算出您可能需要的查询/ DML。

正如菲利普·德沃斯( Filip )所写的那样，你的“财产袋”方法很难处理典型的关系查询--“从那些课程=‘数学’和分数>12的学生中选择计数(*)将是一个巨大的痛苦。”

另一方面，您的初始设计确实解决了有关存储模式在设计时变化或未知的数据的问题。

在实践中，您通常会在一个典型的关系模型中建模“固定的”内容，并使用属性包或XML文档来对变量位进行建模。如果您可以在设计时弄清楚模式，“稀疏列”也可能会有所帮助。