StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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:</p> <p><a href="http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server" rel="nofollow noreferrer">http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server</a></p> <p>...but I will adapt the design pattern in that article to your specific case below.</p> <p>First, you need a new table to hold your possible list of UploadTypes:</p> <pre><code>create table UploadType ( UploadTypeID int primary key, UploadTypeDesc varchar(50) ) </code></pre> <p>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:</p> <pre><code>create table MasterTable ( MasterTableID int primary key, UploadTypeID int references UploadType(UploadTypeID), -- ...Other fields... constraint MasterTable_AltPK unique (MasterTableID,UploadTypeID) ) </code></pre> <p>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):</p> <pre><code>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) ) </code></pre> <p>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.</p> <p>The beauty of this design is that it gives you database-driven 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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload