I don't think a database should be responsible for stopping someone from misclassifying the sub-type of a record. That seems much more like a business logic layer problem.

When I think about "data consistency" from a RDBMS standpoint I think about ACID.

"Consistency. A transaction either creates a new and valid state of data, or, if any failure occurs, returns all data to its state before the transaction was started."

It should have more to do with the mechanics of correctly persisting transactions.

I do wonder about what would be the best way to handle the case of re-classifying a product. Any sub-type record that was previously created would need to be deleted since only one sub-type should exist for each super-type record - assuming only a single layer of inheritance. Should that be a database trigger or something handled in the Business Logic Layer?

I don't think a database should be responsible for stopping someone from misclassifying the sub-type of a record. That seems much more like a business logic layer problem. So, I don't see too big of a problem with your second scenario. I've seen it work pretty well in even very large enterprise solutions.

At large table size (like 2 million or more), the JOIN query that will be necessary to combine sub-class and super-class is going to have trouble scaling with ad-hoc queries if you need to join more than a few thousand results between the two tables. For that reason a Table-per-hierarchy model might be better - the object inheritance is flattened into a single physical table.

I do wonder about what would be the best way to handle the case of re-classifying a product in your example. Any sub-type record that was previously created would need to be deleted since only one sub-type should exist for each super-type record - assuming only a single layer of inheritance. Should that be a database trigger or something handled in the Business Logic Layer?