If all variables are a subset of the candidate key, is the database design 5NF

database-designnormalization

I have a table called LogMessages, which has the following columns:

  • Level
    • A numeric value which represents Trace, Debug, Info, Warning, Error or Fatal
  • Time
    • A UTC time
  • Message
    • Foreign key to a Messages table
  • Source
    • Foreign key to a Sources table
  • User
    • Foreign key to a Users table

From what I can see, all of these columns are a part of the candidate key; if any single value differs to an existing row, a new row can be created.

My question is, does this design comply to fifth normal form? I am unsure as some groups of data will be repeating, however I don't believe this violates 5NF? (correct me if I'm wrong)

Update

It is possible to create a new row in this table if any single one of the columns changes, e.g. It is possible to have the same message at a different level ('User does not exist' could be logged as a warning at one point in time and an error at a different time).

Best Answer

If all variables are a subset of the superkey, is the database design 5NF?

In any 1NF table (does not have duplicate rows) the whole header is a superkey. Any set of attributes (columns) from that table is a subset of that superkey. If your reasoning were right that would mean that any 1NF table is in 5NF. So, this is not correct. But, you are probably just misusing terminology.

What you are probably asking is what if the whole header is the (candidate) key -- keep in mind that a key is irreducible.

General answer to this question is again no; just because a table is all-key it does not make it automatically in 5NF -- all key tables are in BCNF (3.5). They are likely to be in 5NF too, but not as a rule.

Given the info you have provided, I am not sure if your table is all-key (in BCNF). For example, is there a FD {Message} --> {Level} ?

So to answer this properly, you would have to provide sample data and some spelled-out "business" rules.

Take a look at

https://stackoverflow.com/questions/18029821/how-to-understand-the-5th-normal-form