代码之家 › 专栏 › 技术社区 › Stephane

在SQL中描述变量类型的最佳方法

sql

Stephane · 技术社区 · 15 年前

在SQL表结构中,转换以下问题的最佳方法是什么:

在文件传输应用程序中,我有一个带有“上载类型”字段的主表。根据该字段的值(ftp、sftp、https、fs copy),记录将与包含相关详细信息的相应表(ftpsites、httpssites等)中的其他条目链接。

这个主表有几个类似的“切换”字段(上载、下载、加密、解密,以及一些与应用程序相关的字段)。

目前,每个可能的目标表都有一个不同的字段。这允许我在表上保留完整性约束,但有很多字段将为空。

有没有更好的方案来解决这个问题?

如果相关,目标数据库是MS SQL 2008

1 回复 | 直到 12 年前

KP Taylor 12 年前

您所描述的是一个类似于实现表继承的数据库设计问题(其中主表是父表,特定于类型的表是子表)。您可以在这里看到如何使用SQL Server 2005/2008实现表继承的非常好的解释:

http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server

…但是我将根据您下面的具体情况调整文章中的设计模式。

首先,您需要一个新表来保存可能的上载类型列表:

create table UploadType 
(
    UploadTypeID int primary key,
    UploadTypeDesc varchar(50)
)

现在,请确保主表具有UploadType表的外键,并在“MasterTableID”和“UploadTypeID:

create table MasterTable
(
  MasterTableID int primary key, 
  UploadTypeID int references UploadType(UploadTypeID), 
  -- ...Other fields...
  constraint MasterTable_AltPK unique (MasterTableID,UploadTypeID)
)

假设您在uploadType表中插入了值,以便HTTP上载的uploadTypeID=1,FTP上载的uploadTypeID=2,而SFTP上载的uploadTypeID=3,那么现在可以按如下方式设置上载特定的表(末尾的说明):

create table HTTPSites 
(
  HTTPSiteID int primary key, 
  UploadTypeID as 1 persisted, -- computed column; explanation below
  -- ...Other fields...
  foreign key (MasterTableID, UploadTypeID) references MasterTable(MasterTableID, UploadTypeID)
)

create table FTPSites 
(
  FTPSiteID int primary key, 
  UploadTypeID as 2 persisted,
  -- ...Other fields...
  foreign key (MasterTableID, UploadTypeID) references MasterTable(MasterTableID, UploadTypeID)
)

create table SFTPSites 
(
  SFTPSiteID int primary key, 
  UploadTypeID as 3 persisted,
  -- ...Other fields...
  foreign key (MasterTableID, UploadTypeID) references MasterTable(MasterTableID, UploadTypeID)
)

每个特定于类型的表都包含MasterTableID和UploadTypeID上主表的双键外键(这是获得引用完整性的方法),每个表都包含反映存储在该表中的特定上载类型的UploadTypeID计算列。这些计算列中的每一列都将强制使用特定的uploadtypeid创建插入到这些特定类型表中的任何新记录,从而将表锁定到特定的上载类型。

这种设计的好处在于,它为您提供了数据库驱动的引用约束,这些约束可以满足您所有的数据完整性要求,而不需要很多空值。如果您想更深入地了解这个模式如何在插入、删除等过程中防止数据完整性问题,您可以看到上面发布的文章中的详细示例。