This question regards the proper use of NULL and utilizing CHECK constraints for business logic vs stored procedures.
I have the following tables setup.
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?
Best Answer
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.