SQL Server – Moving Nullable Fields to Separate Table

database-diagramsnormalizationsql server

My website has a form page that has radio buttons yes|no corresponding to 7 questions. When the user selects yes, the user needs to specify a value (either a location, name, or medical condition, depending on the question).

However, I'm torn whether I should include the specified values in the same table as the answers.

One one hand, when I include the specified values into the same table as the answers, it is easier to query.

One the other hand, the specified values can have null values (when the user selects yes).

I went with the latter and made 7 tables for each of the specified values. Refer to this diagram:

schema

After looking at the table, I feel it's a bit of an overkill just for a simple functionality.

I would like suggestions as to what route should I go.

Should I merge everything into one table?

–OR–

Should I stick with this diagram?

Best Answer

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.

Related Solutions

Reporting Table Design

I would do something like this:

period_start_on date
period_duration interval
position        int -- position of this row in the year,
                    -- e.g. 1 for first Monday of Oct,
                    -- 2 for 2nd Monday, etc.
values ...
primary key(period_start_on, period_duration)
check(position >= 1 and position < 40)

Then if you want December only, you use WHERE period_duration = '1d' and period_start_on >= '2012-12-01' AND period_start_on < '2013-01-01'. If you want to compare two years, you JOIN using position:

SELECT y1.*, y2.*
FROM   the_table y1
  JOIN the_table y2 USING (position)
WHERE y1.period_start_on >= '2011-01-01' AND y1.period_start_on < '2012-01-01'
  AND y2.period_start_on >= '2012-01-01' AND y2.period_start_on < '2013-01-01'

Database Design – Preventing Duplicate Data with Selective Normalization

If I understand your dilemma correctly, you have:

Two tables, each of which can have a comment
One of the tables is optional, i.e. it may not have an entry to correspond to the other
For the second table, the comment is also optional, such that even if the second table has a record to match the first, the comment in the second may just "default" to a copy of the first.
On the other hand, the second table may have a distinct comment after all.

If this is the case, your concern is that recording the "default" (duplicate) comment in the second table is wasteful or even dangerous since the data could get out of whack.

In this situation, you can use the SQL COALESCE function along with a LEFT OUTER JOIN to solve your problem.

Using the outer join lets you use a single SQL statement that pulls together the two tables (when there are records in each) or just pull data from the first (mandatory) table if the second (optional) record is missing. No complicated branching, just a single SQL select. The coalesce allows you to pick the first non-NULL value in a list of values. This is useful because it lets you take the first comment as a default if the second comment is NULL. The second comment can be NULL because either (a) there is no matching record in the second table or (b) the comment within the second table is NULL.

It seems like in your case there will always be an ISSUE entry. Sometimes this entry is created by a user and sometimes it is the result of an unsatisfactory STATUS_CHECK.

In this case you want to select:

...
COALESCE(I.comment, C.comment) as Remark
FROM ISSUE I LEFT OUTER JOIN STATUS_CHECK C
ON I.ID = C.ISSUE_ID
...

The net effect of this is that if you have a satisfactory status check, it will show the status check comment. If you have an unsatisfactory status check or an independently raised issue, it will show the (non-NULL) comment from the issue table.

Best Answer

Related Solutions

Reporting Table Design

Database Design – Preventing Duplicate Data with Selective Normalization

Related Question