3

This question regards the proper use of NULL and utilizing CHECK constraints for business logic vs stored procedures.

I have the following tables setup.

Entity Relationship Diagram

I normalized the tables to avoid using NULLs. The problem is that some of these tables depend on each other due to business processes. Some devices must be sanitized, and some are tracked in another system. All devices will eventually be disposed in the Disposal table.

The issue is that I need to perform checks, such as if the boolean field RequiresSantization is true, then the DisposalDate cannot be entered until the Sanitize fields are entered.

Also, if the boolean value IsTrackedInOther is true, then the OfficialOutOfService fields must be entered before the DisposalDate can be entered.

If I merge all of these columns into the Archive.Device table then I will have NULL fields, but I will be able to manage all of the business rules using CHECK constraints.

The alternative is to leave the tables as they are, and manage the business logic in the stored procedure by selecting from the tables to check if records exist and then throw appropriate errors.

Is this a case where NULL can be used appropriately? The boolean fields IsTrackedInOther and RequiresSanitization basically give meaning to the NULL fields. If IsTrackedInOther is false then the device is not tracked in the other system and SectionID and SpecialDeviceCode are NULL, and I know that they should be NULL becuase it is not tracked in the other system. Likewise, OfficialOutOfServiceDate and OOSLogPath I know will be NULL aswell, and a DisposalDate can be entered at any time.

If IsTrackedInOther is true, then SectionID and SpecialDeviceCode will be required, and if OfficialOutOfServiceDate and OOSLogPath are NULL, then I know they have not been officially removed from that system yet and thus cannot have a DisposalDate until they are entered.

So it's a question between

separate tables/no NULLs/enforce rules in stored procedures

vs

combined table/NULLs/enforce rules in CHECK constraints.

I understand that querying with NULLs in the picture can be complex and have somewhat undefined behavior, so separate tables and stored procedures seem beneficial in that sense. Alternatively, being able to use CHECK constraints and have the rules built into the table seems equally beneficial.

Any thoughts? Thanks for reading. Please ask for clarification where needed.

Update Example table if they were merged and I allowed NULLs.

* = Allow NULL

+-------------------------+
|Archive.Device Table     |
+-------------------------+
|DeviceID                 |
|SerialNumber             |
|DeviceTypeID             |
|IsTrackedInOther         |
|SectionID*               |
|SpecialDeviceCode*       |
|OfficialOutOfServiceDate*|
|OOSLogPath*              |
|OOSRemarks*              |
|RequiresSanitization     |
|SanitizeMethodID*        |
|SanitizeLogPath*         |
|SanitizeDate*            |
|SanitizeRemarks*         |
|Location                 |
|OriginalInventoryDate    |
|ArchiveDate              |
|LastUpdated              |
|ReasonID                 |
|StorageLocation          |
|ArchiveRemarks           |
|CategoryCode             |
+-------------------------+

Example CHECK:

IsTrackedInOther = 0 AND SectionID IS NULL AND SpecialDeviceCode IS NULL AND 
OfficialOutOfServiceDate IS NULL AND OOSLogPath IS NULL AND OOSRemarks IS NULL
OR IsTrackedInOther = 1 AND SectionID IS NOT NULL AND SpecialDeviceCode IS NOT NULL

Does this seem a natural way to handle this, or is this a design problem?

Improve this question
4
  • 4
    Don't bend over backward to avoid NULLs. They're perfectly appropriate in many cases, such as where an event has not yet occurred.
    – Jon of All Trades
    Jul 26, 2012 at 19:19
  • 3
    I would always prefer a solution where the rules are defined statically (i.e check constraints) over a procedural check.
    – user1822
    Jul 26, 2012 at 20:48
  • @JonofAllTrades That is what I was thinking. Assuming I merged all of the tables into one to use CHECKs. What threw me off was this: The IsTrackedInOther boolean field, when true says that the SectionID and SpecialDeviceCode require values. Additionally, the OfficialOutOfServiceDate and OOSLogPath would be NULL until filled in the future. If IsTrackedInOther is set to false, then SectionID, SpecialDeviceCode, OfficialOutOfServiceDate, and OOSLogPath should never be entered and they will all be NULL. I am not experienced enough to know if this design is okay or presents hidden problems.
    – TheSecretSquad
    Jul 26, 2012 at 21:19
  • 1
    @a_horse_with_no_name I agree. There is something about hiding important business rules in stored procedures that just doesn't sit right with me. I feel that if I'm able to enforce integrity statically, as you say, then I should take every chance to do so; even if it makes some queries more difficult because of the introduction of NULLs. I would have to merge all of these tables to utilize CHECKs, which would introduce 10 fields that could all possibly be NULL (depending on the the boolean fields). I am led to believe by other resources to "avoid NULLs like the plague" (actual quote).
    – TheSecretSquad
    Jul 26, 2012 at 21:36

2 Answers 2

Reset to default
6

Querying a column that can contain nulls is more complex than querying a column that cannot. So also is querying multiple tables more complex than querying one table. I wouldn't let the avoidance of null drive the normalization.

For example, you mentioned that all devices will eventually be disposed and get a disposal date. If there are no other columns in the Disposal table, then in my mind it makes more sense to put DisposalDate in the Device table. Other tables like Sanitize might make more sense as separate tables because there are multiple data points that will not apply to some Devices.

Check constraints are great and should be used when possible, but there will always be times when a procedure is necessary.

Improve this answer
4

I would go for the check constraints, you cannot be positive that records will never get manually entered or adjusted. Data integrity trumps avoiding nulls every single time.

Improve this answer
1
  • That was one of my concerns. Stored procedures can be bypassed by another dba, or rules can be broken when stored procedure code is changed. In my opinion, the table should define what data it holds and what is acceptable in terms of data entered. In order for me to use CHECK constraints I would have to introduce many nullable columns to the table. So much of what I read says to avoid NULLs at all cost leading me to this dilemma.
    – TheSecretSquad
    Jul 27, 2012 at 11:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.