Best way to describe variable types in SQL

Question

Hello,

What is the best way to translate the following problem in SQL table structure:

In a file transfer application, I have a master table with an "Upload type" field. Depending on the value of that field (FTP, SFTP, HTTPS, FS copy) the record is to be linked with other entries in the appropriate table (FTPsites, HTTPSSites, etc.) containing the relevant details.

This master table has several similar "switch" fields (upload, download, encryption, decryption, and a few application-related ones).

Currently, the table has a different field for each possible target table. This allows me to keep integrity constrains on the table but that's a lot of fields which are going to be NULL.

Is there a better schema for solving that problem ?

In case it's relevant, the target DB is MS SQL 2008

Answer 1

What you are describing is a database design issue akin to implementing table inheritance (where your master table is the parent and your type-specific tables are the children). You can see a really good explanation of how to implement table inheritance with SQL Server 2005/2008 here:

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

...but I will adapt the design pattern in that article to your specific case below.

First, you need a new table to hold your possible list of UploadTypes:

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

Now, make sure your MasterTable has a foreign key to the UploadType table and add an additional UNIQUE constraint to your master table on the fields MasterTableID and UploadTypeID:

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

Assuming you have inserted values into the UploadType table so that HTTP uploads have an UploadTypeID = 1, FTP uploads have an UploadTypeID = 2, and SFTP uploads have an UploadTypeID = 3, you can set now up your upload-specific tables as follows (explanation at the end):

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)
)

Each of these type-specific tables includes a dual-key foreign key to the master table on the MasterTableID and the UploadTypeID (this is how you get your referential integrity), and each includes a computed-column for the UploadTypeID that reflects the specific type of upload stored in that table. Each of these computed columns will force any new records inserted into these type-specific tables to be created with a specific UploadTypeID, therefore locking the tables to a specific upload type.

The beauty of this design is that it gives you database-drive referential constraints that meets all of your data integrity requirements without a lot of nulls. You can see the above posted article for detailed examples of how this schema prevents data integrity problems during inserts, deletes, etc. if you want to go deeper.