通用数据库设计方法 [英] Approach to generic database design
问题描述
我在客户面前的应用程序如下所示：
An application that I'm facing at a customer, looks like this:
- 它允许最终用户输入资料。
- 对于这些资料，他们可以附加任何数量的属性。
- 属性可以具有以下类型的任何值：decimal，int，dateTime和varchar（长度从5个字符变化到大块文本），
本质上，架构如下所示：
Essentially, the Schema looks like this:
素材
MaterialID int not null PK
MaterialName varchar（100）not null
Materials
MaterialID int not null PK
MaterialName varchar(100) not null
属性
PropertyID
PropertyName varchar（100）
Properties
PropertyID
PropertyName varchar(100)
MaterialsProperties
物料ID
PropertyID
PropertyValue varchar（3000）
MaterialsProperties
MaterialID
PropertyID
PropertyValue varchar(3000)
应用程序的一个基本功能是搜索功能：
最终用户可以通过输入以下查询来搜索资料：
An essential feature of the application is the search functionality: end users can search materials by entering queries like:
- [property] inspectDate> [DateTimeValue]
- [property] serialNr = 35465488
猜测它在MaterialsProperties表中的表现如何，其中有近200万条记录。
Guess how this performs over the MaterialsProperties-table with nearly 2 million records in it.
数据库最初创建在SQL Server 2000和更高版本上迁移到SQL Server 2005
Database was initially created under SQL Server 2000 and later on migrated to SQL Server 2005
如何更好地实现？
推荐答案
- 由于用户可以输入自己的属性名称，我猜每个查询将涉及到属性表的扫描（在您的示例中，我需要找到属性[检验日期]）。如果属性表很大，您的连接也需要很长时间。您可以通过使用propertyID进行非规范化和存储名称来尝试和优化。这将是MaterialsProperties表中的一个反归一化的列。
- 您可以尝试向materialsproperty表添加属性类型（int，char等），并对类型进行分区。
- 查看对象关系映射/实体属性值查询优化的模型技术。
- 由于您已经拥有大量数据（200万条记录），因此可以进行一些数据挖掘，以查看许多材料是否存在重复的属性组。您可以将它们放在一个模式中，其余的作为EAV表。详情请看这里： http://portal.acm.org/citation.cfm?id=509015&dl=GUIDE&coll=GUIDE&CFID=49465839&CFTOKEN=33971901
- Since users can enter their own property names, i guess every query is going to involve a scan of the properties table (in your example i need to find the propertyid of [inspectionDate]). If the properties table is large, your join would also take a long time. You could try and optimize by denormalizing and storing name with propertyID. This would be a denaormalized column in the MaterialsProperties table.
- You could try adding a property type (int, char etc) to the materialsproperty table and partition the table on the type.
- Look at Object Relational Mapping/Entity Attribute Value Model techniques for query optimization.
- Since you already have a lot of data (2 million records) do some data mining as see if there are repeating groups of properties for many materials. You can them put them in one schema and the rest as the EAV table. Look here for details: http://portal.acm.org/citation.cfm?id=509015&dl=GUIDE&coll=GUIDE&CFID=49465839&CFTOKEN=33971901
这篇关于通用数据库设计方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
