Home » SQL & PL/SQL » SQL & PL/SQL » normalization question need for nullable column ?
normalization question need for nullable column ? [message #381013] Wed, 14 January 2009 15:53 Go to next message
Messages: 202
Registered: April 2006
Senior Member
I have a table Audit with a column audit_review_code

This column I use to determine if the audit record was rejected or approved .

We also have to record reason for rejection.

to facilitate this I proposed a new column col_X telling reason for rejection and in case of approved col_X will remain null and in case of rejected col_X will have a value.

in this case possible values for audit_review_code are

My coworker proposed lets not have additional column because we have to leave col_X null and have codes which will also indicate reason for rejection


In over real case there are only few reasons for rejection not more than 5 , So please help , which is the better approach and why ?

Re: normalization question need for nullable column ? [message #381018 is a reply to message #381013] Wed, 14 January 2009 19:18 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
You need to distinguish between Logical and Physical designs. What you describe is a "subtype" of your audit entity. The subtype means that extra attributes only apply depending on the value of something else in the record. e.g. a person could be an employee type or a customer type (in the same entity). Manager attribute would only apply to employees, not customers or whatever...

The physical implementation could be 1 or 2 tables (1 for the common fields [incl the discriminator col] and 1 for the subtype fields with an FK between the 2).

I think your physical design will be dictated by the queries you want to run. Your first design will need you to find all rejected records and having found those, look at the detail reason to find more detail. When you add a new detail reason type, you can still distinguish between approved/rejected by looking at that field.

Keeping just one field like in the second design means you need to identify rejects using "where audit_review_code in ('REJECTED_NO_EIN', 'REJECTED_NO_DUNS')" or even worse "where audit_review_code like 'REJECTED%'" etc. That needs maintaining when you change the permitted values. The advantage is you only have a single column to worry about which is easy for all to understand.

Another angle to consider is screen & reports layouts. Does the business user like to see it as 1 or 2 fields?

I've used both designs, but lean towards the more simple design even if the SQL need a bit more maintenance...
Previous Topic: SCD Via Stored Procedures in Oracle
Next Topic: Trigger lost when View Re-Created
Goto Forum:

Current Time: Tue Oct 25 22:03:35 CDT 2016

Total time taken to generate the page: 0.13567 seconds