1

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.

  • What I see are two distinct things. Users and privileges. Thus, I would have a users table, a privilege table and a UserPriveleges table to say who gets what. This will keep your tables nice and clean with minimal null values. – Eric S Aug 22 '16 at 18:48
  • Thanks Eric. This design is just a straw man and I am asking a specific (and somewhat academic) question about normalization. – John Wu Aug 22 '16 at 18:53
  • Yes it violates 3NF. – Randolph West Aug 22 '16 at 22:15
  • Maybe sixth normal form? – Michael Green Aug 23 '16 at 1:33
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.