My post on Disjoint Subtypes generated some interesting feedback.
I made a point of saying that the Products table in my schema is in BCNF. That’s because the only determinants are supersets of {SKU}, which is the only key. The point I wanted to make was not about normalization as such but about the nature of SQL UNIQUE and PRIMARY KEY constraints. There are two such constraints in this case but only one key. In SQL the UNIQUE and PRIMARY KEY constraints are superkey constraints, not key constraints. In other words, they are unique but they are not required to be irreducibly so. There is no reason to doubt that Products is in BCNF, as long as you understand that a UNIQUE constraint is not the same as a candidate key (and a PRIMARY KEY is not the same as a Primary Key).
However, Johan Dufour commented that the Books, CDs and DVDs tables do all suffer from a non-key dependency { } -> {ProductType} therefore they are not in BCNF. I hadn’t considered before that this dependency on the empty set was a formal violation of BCNF (somehow I felt that it could be dismissed as a consequence of a domain constraint) but I now agree that is. As a result my assertion about the whole schema being in 5NF was an error.
The redundancy of ProductType in each table plus the potential overhead of the constraints and other logic needed to support it are always going to be the obvious drawbacks of the scheme. Despite this I have found it to be a worthwhile technique, given the limitations of the constraints available in most SQL products. I’m going to follow up with another entry on problems with certain other constraints in SQL Server.
Thanks to Hugh Darwen, Johan Dufour, Claes Welander and others on The Third Manifesto mailing list for their valuable contibutions to the discussion.