Do conditional 1:1 relationships improve normalization?

Question

Imagine a database schema that includes (among other things) two tables:

USER - A table of users, including their signon ID, name, etc. The primary key is a surrogate key, "UserID" (auto integer).

ADMIN - A table of administrators, including a series of columns that a USER does not need but an ADMIN does, e.g. "SuperuserPassword" which is a hash of a password used for elevated permissions (this is just an example). The primary key of this table is also "UserID" and is a foreign key to USER.UserID.

These two tables technically have a 1:1 relationship, although not every USER record will have an ADMIN record. I believe this sort of relationship is referred to as a variant record, or in C terms it might be called a tagged union. Essentially USER is a supertype and ADMIN is a subtype.

In the above scenario, assume both tables are in BCNF.

Now for my question. Imagine a second scenario, where the DBE was a little bit lazier, and has all of the above data combined into a single table USER. If the record pertains to a non-admin user, the SuperuserPassword field is simply left NULL.

In the second scenario, does the table design violate normalization rules? Which type of normalization does it violate?

I am thinking there is an implied attribute here (user type) which can be inferred by the presence of the admin record. Since the SuperuserPassword is an attribute that depends on a non-key [implied] attribute, the second design is a violation of third normal form. But I am not certain this is technically accurate.

Please note, this is a completely academic question, and I am not asking for advice on this table design, which is just a straw man.

Answer 1

Normal forms based on functional dependencies (e.g. BCNF) are defined for relation schemas and relations consisting of values without nulls. As soon as you permit nulls you already have something that is not a normalized relational database.

In your first scenario, if your ADMIN table has (non-nullable) attributes, X,Y,Z, then the dependency {UserID}->{X,Y,Z} is enforced and will be satisfied for every admin user. If you combine the tables and make X,Y,Z nullable then those attributes may be null even for admin users. The dependency {UserID}->{X,Y,Z} is therefore no longer automatically enforced if you combine the tables. Assuming that dependency is important to you then you would have to add extra integrity constraints to validate that each of those attributes has a value for admin users and a null for non-admins. The advantages of BCNF (i.e. having both ADMIN and USER tables) are that the intended business rule {UserID}->{X,Y,Z} is declared as part of your table design, is obvious to anyone inspecting the schema and is guaranteed simply by the presence of keys and non-nullable attributes in your tables.