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
RequiresSanitization basically give meaning to the NULL fields. If
false then the device is not tracked in the other system and
SpecialDeviceCode are NULL, and I know that they should be NULL becuase it is not tracked in the other system. Likewise,
OOSLogPath I know will be
NULL aswell, and a
DisposalDate can be entered at any time.
SpecialDeviceCode will be required, and if
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
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 | +-------------------------+
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?