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.
Constraints and indexes are two entirely different things. A constraint defines what can go in the column and an index creates an internal structure to help performance.
-- (1)
create table TestTable (
ID autoincrement primary key
, F int not null
);
This is part of the table/column definition. Probably the easiest and most common way to restrict a column from null values.
or:
-- (2)
create table TestTable (
ID autoincrement primary key
, F int
, constraint cnnF not null (F)
);
This is a constraint. This will also work but it's really meant for things like making sure a char(1) column only has a Y or an N in it.
or:
-- (3)
create table TestTable (
ID autoincrement primary key
, F int
);
create index idxF on TestTable (F) with disallow null;
This is an index. As a side effect it is disallowing null but really it's purpose is is to speed up your queries.
I hate to say it but lazy isn't going to work. I would put null/not null definitions into the column definition. Then use constraints on columns where you want to restrict what values go into the column, not if it's null or not. Then last but not least add indexes where needed for query performance.
Best Answer
In reference with below Employee Entity Relational Model
Overlap Constraints : Can "Karthik" (employee) be an Hourly_Emps as well as a Contract_Emps entity? (Allowed/Disallowed) - In this scenario the hourly employee karthik cannot be a contract employee - Disallowed - This explains the Overlap constraints.
Covering Constraints : Is every hourly employee and every contract employee an employee of this organization? Answer: Yes, all the contract/hourly employees belong to the master employees entity - This explains the Covering constraints.