Simple Database normalization question...

Question

Hi all,

I have a quick question regarding a database that I am designing and making sure it is normalized...

I have a customer table, with a primary key of customerId. It has a StatusCode column that has a code which reflects the customers account status ie. 1 = Open, 2 = Closed, 3 = Suspended etc...

Now I would like to have another field in the customer table that flags whether the account is allowed to be suspended or not... certain customers will be automatically suspended if they break there trading terms... others not... so the relevant table fields will be as so:

Customers (CustomerId(PK):StatusCode:IsSuspensionAllowed)

Now both fields are dependent on the primary key as you can not determine the status or whether suspensions are allowed on a particular customer unless you know the specific customer, except of course when the IsSuspensionAllowed field is set to YES, the the customer should never have a StatusCode of 3 (Suspended).

It seems from the above table design it is possible for this to happen unless a check contraint is added to my table. I can't see how another table could be added to the relational design to enforce this though as it's only in the case where IsSuspensionAllowed is set to YES and StatusCode is set to 3 when the two have a dependence on each other.

So after my long winded explanation my question is this: Is this a normalization problem and I'm not seeing a relational design that will enforce this... or is it actually just a business rule that should be enforced with a check contraint and the table is in fact still normalized.

Cheers,

Steve

Answer 1

Yes it is possible. You can do it with a check constraint and a Case statement:

Create Table Customer   (
        CustomerId <datatype> not null Primary Key
        , StatusCode int not null
        , IsSuspensionAllowed int not null Default( 1 )
        , Constraint CK_Customer_IsSuspensionAllowed 
                    Check ( IsSuspensionAllowed In(0,1) )
        , Constraint CK_Customer_StatusCodeRange 
                    Check ( StatusCode Between 0 And ?? )
        , Constraint CK_Customer_StausCodeValid 
                    Check ( Case
                When StatusCode = 3 And IsSuspensionAllowed = 1 Then 0
                                                                Else 1
                                                                End = 1 )
        , ....
        )

You did not mention the data type of the PK so I simply inserted a placeholder. If you are using SQL Server, you can use a bit column in lieu of the int and check constraint I mentioned above (bit is not part of the ANSI spec).

This is a good example of where surrogate keys for things like status codes do not always work out well. It would be better to have string values represent the status codes in which case the Case statement would read When StatusCode = 'Suspended' And IsSuspendedAllowed = 0....

From a normalization standpoint, I do not see anything wrong. Whether suspension is allowed is an attribute specific to a Customer and not another attribute. What you are saying with the check constraint, that certain states of attribute values cannot exist which is fine.

Btw, wouldn't make more sense to say that the Status of "Suspended" is not allowed when IsSuspensionAllowed = 0? Using your data, shouldn't the state that is not allowed be StatusCode = 3 and IsSuspensionAllowed = 0?