SQL Server 2012 – Handling NULLable Fields with Sensitive Data

check-constraintsdata integritydatabase-designsql serversql-server-2012

I have a requirement for a database table to hold sensitive patient information about their lifestyle (alcohol/ drug use / injecting etc).

On screen the information is sectioned into tabs – "alcohol", "drugs", "injecting" and so on. Some of the data is co-dependent (ie: data in one tab may depend on what is input in another).

I was considering storing all of the data captured in the tabs (circa 25 fields – maybe more, but not much more) in a single table, because of the aforementioned co-dependency. All of the fields in the table would be NULLable (edit: for two reasons: (1) because people might have alcohol problems but not drug problems and vice versa and (2) the data can be in DRAFT or PRODUCTION state – in draft incomplete data is acceptable, but in production relevant data must be supplied)

To improve data integrity I would employ CHECK constraints to ensure that data was supplied for sets of fields in the table IF certain BIT flags were set (e.g. we are in PRODUCTION mode and a flag named HasUsedAlcohol is set, this mandates all alcohol related fields be populated; HasUsedDrugs would mandate all drug related fields be populated)

Additionally this table may be used to produce reports.

My question is – is this a sensible, maintainable solution? And if not, why not?

Database target is SQL Server 2012.

Thanks in advance.

Best Answer

I wouldn't be too concerned about 25 columns. That's not an extremely high number. It doesn't even approach violating Swart's Ten Percent Rule (102 columns). Of course, the "rule" there is discussing a limit, not a goal, so keep that in mind ?

In my mind, ideally, each of these separate "forms" (tabs) would be backed by its own table (AlcoholAbuse, DrugAbuse, Injections, etc), which would then be linked back to the patient by a foreign key constraint.

However, you mentioned that there are data validation concerns that cross forms. Maybe if you answer "yes" to certain questions on the drugs form, then certain questions on the injections form are required.

Since CHECK constraints are really the best way to enforce what you're describing, that would be mean they all need to be in the same table.

Your other options, if things were in separate tables, would be to move validation into the application layer, or use triggers to ensure the data in the different form tables is valid on each insert / update.

Since the number of columns isn't outrageous, and you probably don't want to incur the complexity of dealing with triggers, I think one table with conditional CHECK constraints is a valid solution.

This solution seems maintainable to me, and fairly easy to reason about, since all of the "rules" are in one place (one table, and the constraints applied to that one table).

Related Solutions

MySQL – Database Redesign for Sensor Data Collection

You should think about partitioning the table for a big reason.

All indexes you have on a giant table, even just one index, can generated a lot of CPU load and disk I/O just to perform index maintenance when executing INSERTs, UPDATEs, and DELETEs.

I wrote an earlier post back on October 7, 2011 on why Table Partitioning would be a big help. Here is one excerpt from my past post:

Partitioning of data should serve to group data that are logically and cohesively in the same class. Performance of searching each partition need not be the main consideration as long as the data is correctly grouped. Once you have achieved the logical partitioning, then concentrate on search time. If you are just separating data by id only, it is possible that many rows of data may never be accessed for reads or writes. Now, that should be a major consideration: Locate all ids most frequently accessed and partition by that. All less frequently accessed ids should reside in one big archive table that is still accessible by index lookup for that 'once in a blue moon' query.

You can read my entire post later on this.

To cut right to the chase, you need to research and find out what data is rarely used in your 10GB table. That data should be placed in an archive table that is readily accessible should you need adhoc queries for a historical nature. Migrating that archival from the 10GB, followed by OPTIMIZE TABLE on the 10GB table, can result in a Working Set that is faster to run SELECTs, INSERTs, UPDATEs, and DELETEs. Even DDL would go faster on a 2GB Working Set than a 10GB table.

UPDATE 2012-02-24 16:19 EDT

Two points to consider

From your comment, it sounds like normalization is what you may need.
You may need to migrate out everything over 90 days old into an archive table but still access archive and working set at the same time. If your data is all MyISAM, I recommend using the MERGE storage engine. First, you create the MERGE table map once that unites a working set MyISAM table and an archive MyISAM table. You would keep data less than 91 days in one MyISAM table and rollover any data over 90 days old into the archive. You would query the MERGE table map only.

Here are two posts I made on how to use it:

Here is an additional post I made on tables with a lot of columns

Too many columns in MySQL

SQL Server – Moving Nullable Fields to Separate Table

Rather than stick with that table, would creating a question table work for your application? That is, instead of having a separate table for each question, have a question table as

CREATE TABLE question (question_id numeric, question text)

and instead of the AFE table linking to a bunch of other tables, create a separate answers table as

CREATE TABLE answers(question_id numeric, afe_id numeric, answer text)

That acts as a bridge between AFE and question. This lets you add new questions easier too.

Best Answer

Related Solutions

MySQL – Database Redesign for Sensor Data Collection

UPDATE 2012-02-24 16:19 EDT

SQL Server – Moving Nullable Fields to Separate Table

Related Question