Proper use of NULL, and utilizing CHECK constraints for business logic vs stored procedures

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

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.

StackExchange Database Administrators Q#21567, answer score: 6