Have Geek Brain

The best way to describe variable types in SQL

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

In my file transfer application, I have a main table with a Upload Type field. Depending on the value of this field (FTP, SFTP, HTTPS, FS copy), the entry must be linked to other entries in the corresponding table (FTP sites, HTTPSSites, etc.) containing the relevant data.

There are several similar "switch" fields in this master table (upload, download, encrypt, decrypt and a few related to the application).

Currently the table has a different field for every possible target table. This allows me to keep the integrity constraints of the table, but many fields will be NULL.

Is there a better circuit for solving this problem?

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

+2


a source to share


1 answer


What you are describing is a database design problem, similar to implementing table inheritance (where your main table is the parent and your type tables are 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'll adapt the design pattern in this article for your specific case below.

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

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

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



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 to upload HTTP files with UploadTypeID = 1, FTP uploads have UploadTypeID = 2 and SFTP uploads have UploadTypeID = 3, you can set your upload related 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 foreign key with two keys in the master table on MasterTableID and UploadTypeID (this is how you get your referential integrity), and each includes a computed column for UploadTypeID that reflects a specific type download stored in this table. Each of these computed columns will cause any new records inserted into these type tables to be created with a specific UploadTypeID, so locking tables for a specific upload type.

The beauty of this design is that it provides database-driven relational constraints that meet all your data integrity requirements without a lot of zeros. You can see the article above for detailed examples of how this schema prevents data integrity issues during inserts, deletes, etc. if you want to dive deeper.

+3


a source






More articles:


All Articles