代码之家 › 专栏 › 技术社区 › Scott Whitlock

在关系数据库中,您将特殊属性存储在哪里?

sql

Scott Whitlock · 技术社区 · 14 年前

假设您有一个类似INVENTORY\u ITEM的关系数据库表。这是一般意义上的任何库存需要一个记录在这里。现在让我们假设有成吨不同类型的库存,每种不同类型的库存可能有他们想要跟踪的唯一字段(例如,forks可能会跟踪耙齿的数量,但冰箱不会使用该字段)。对于每个类别类型,这些字段必须是用户可定义的。

有很多方法可以解决这个问题:

使用ALTERTABLE语句实时添加可为空的列(yuk)
有两个具有一对一映射的表INVENTORY\u ITEM和INVENTORY\u ITEM\u USER,并使用ALTER TABLE语句动态地从后一个表中添加和删除可为null的列(稍微好一点)。
添加自定义\u属性表和自定义\u属性\u值表,在用户添加和删除行时在自定义\u属性中添加/删除行,并将值存储在后一个表中。这是很好的和通用的,但性能会受到影响。如果平均每个项有20个值,那么CUSTOM\u PROPERTY\u VALUE中的行数将以20倍的速率增加,并且您仍然需要在CUSTOM\u PROPERTY\u VALUE中为要存储的每个不同数据类型包含列。
在库存项目上有一个大的varchar(MAX)字段以XML形式存储自定义属性。
我想您可以为每个挂起INVENTORY\u ITEM表的category类型创建单独的表,当用户创建INVENTORY类型时,这些表会被动态地创建/销毁,当向这些类型添加/删除属性时,列会被更新。不过看起来很乱。

我是否错过了其他选择?有没有一种方法可以让SQLServer“查看”列中的XML数据,这样它现在就可以使用选项4进行操作了?

4 回复 | 直到 14 年前

Yves M. 14 年前

我在这种情况下使用xml类型列。。。

http://msdn.microsoft.com/en-us/library/ms189887.aspx

在使用xml之前,我们必须使用选项3。在我看来这仍然是一个很好的方法。特别是如果你有一个数据访问层,它能够为你正确地处理类型转换。我们将所有内容都存储为字符串值,并定义了一个列,其中包含用于转换的原始数据类型。

选项1和2是不允许的。不要在生产中动态更改数据库模式。

user151323 user151323 14 年前

肯定是3号。

Damien_The_Unbeliever 14 年前

3或4是我唯一会考虑的-您不想在运行中更改模式,尤其是在使用某种映射层时。

我一般都会选择第三种。作为明智之举,我总是在CUSTOM\u PROPERTY表中有一个type列,它在CUSTOM\u PROPERTY\u VALUE表中重复。通过向<Primary Key,Type>的自定义属性表中添加一个superkey,您就可以拥有一个引用它的外键(以及只引用主键的简单外键)。最后,一个check约束,它确保基于此类型列,只有CUSTOM\u PROPERTY\u VALUE中的相关列不为null。

这样,你就知道如果有人定义了一个int类型的自定义属性,比如,Tine count,你实际上只会找到一个存储在这个属性的所有实例的自定义属性值表中的int。

如果您需要它来引用多个实体表,那么它可能会变得更复杂,特别是如果您想要完全的引用完整性。例如(数据库中有两种不同的实体类型):

    create table dbo.Entities (
        EntityID uniqueidentifier not null,
        EntityType varchar(10) not null,
        constraint PK_Entities PRIMARY KEY (EntityID),
        constraint CK_Entities_KnownTypes CHECK (
            EntityType in ('Foo','Bar')),
        constraint UQ_Entities_KnownTypes UNIQUE (EntityID,EntityType)
    )
    go
    create table dbo.Foos (
        EntityID uniqueidentifier not null,
        EntityType as CAST('Foo' as varchar(10)) persisted,
        FooFixedProperty1 int not null,
        FooFixedProperty2 varchar(150) not null,
        constraint PK_Foos PRIMARY KEY (EntityID),
        constraint FK_Foos_Entities FOREIGN KEY (EntityID) references dbo.Entities (EntityID) on delete cascade,
        constraint FK_Foos_Entities_Type FOREIGN KEY (EntityID,EntityType) references dbo.Entities (EntityID,EntityType)
    )
    go
    create table dbo.Bars (
        EntityID uniqueidentifier not null,
        EntityType as CAST('Bar' as varchar(10)) persisted,
        BarFixedProperty1 float not null,
        BarFixedProperty2 int not null,
        constraint PK_Bars PRIMARY KEY (EntityID),
        constraint FK_Bars_Entities FOREIGN KEY (EntityID) references dbo.Entities (EntityID) on delete cascade,
        constraint FK_Bars_Entities_Type FOREIGN KEY (EntityID,EntityType) references dbo.Entities (EntityID,EntityType)
    )
    go
    create table dbo.ExtendedProperties (
        PropertyID uniqueidentifier not null,
        PropertyName varchar(100) not null,
        PropertyType int not null,
        constraint PK_ExtendedProperties PRIMARY KEY (PropertyID),
        constraint CK_ExtendedProperties CHECK (
            PropertyType between 1 and 4), --Or make type a varchar, and change check to IN('int', 'float'), etc
        constraint UQ_ExtendedProperty_Names UNIQUE (PropertyName),
        constraint UQ_ExtendedProperties_Types UNIQUE (PropertyID,PropertyType)
    )
    go
    create table dbo.PropertyValues (
        EntityID uniqueidentifier not null,
        PropertyID uniqueidentifier not null,
        PropertyType int not null,
        IntValue int null,
        FloatValue float null,
        DecimalValue decimal(15,2) null,
        CharValue varchar(max) null,
        EntityType varchar(10) not null,
        constraint PK_PropertyValues PRIMARY KEY (EntityID,PropertyID),
        constraint FK_PropertyValues_ExtendedProperties FOREIGN KEY (PropertyID) references dbo.ExtendedProperties (PropertyID) on delete cascade,
        constraint FK_PropertyValues_ExtendedProperty_Types FOREIGN KEY (PropertyID,PropertyType) references dbo.ExtendedProperties (PropertyID,PropertyType),
        constraint FK_PropertyValues_Entities FOREIGN KEY (EntityID) references dbo.Entities (EntityID) on delete cascade,
        constraint FK_PropertyValues_Entitiy_Types FOREIGN KEY (EntityID,EntityType) references dbo.Entities (EntityID,EntityType),
        constraint CK_PropertyValues_OfType CHECK (
            (IntValue is null or PropertyType = 1) and
            (FloatValue is null or PropertyType = 2) and
            (DecimalValue is null or PropertyType = 3) and
            (CharValue is null or PropertyType = 4)),
        --Shoot for bonus points
        FooID as CASE WHEN EntityType='Foo' THEN EntityID END persisted,
        constraint FK_PropertyValues_Foos FOREIGN KEY (FooID) references dbo.Foos (EntityID),
        BarID as CASE WHEN EntityType='Bar' THEN EntityID END persisted,
        constraint FK_PropertyValues_Bars FOREIGN KEY (BarID) references dbo.Bars (EntityID)
    )
    go
    --Now we wrap up inserts into the Foos, Bars and PropertyValues tables as either Stored Procs, or instead of triggers
    --To get the proper additional columns and/or base tables populated

supercat 14 年前

如果数据库很好地支持XML,我倾向于将数据存储为XML,或者为不同的数据类型使用少量不同的表(尝试格式化数据,使其适合少量类型中的一种--不要将一个表用于VARCHAR(15),另一个表用于VARCHAR(20),等等),类似于#5,但所有表都是预先创建的,等等所有的东西都塞进了现有的桌子里。每一行应该保存一个主记录ID、记录类型指示符和一段数据。根据记录类型设置索引,按数据排序,可以查询特定的字段值(其中RecType==19,data==Fred)。查询匹配多个字段值的记录会更困难,但这就是生活。