3

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).

1 Answer 1

2

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

3
  • There is no functional dependency between any of the columns, the same message may be a warning one time, and an error somewhere else (e.g. 'User does not exist') may be a warning in some places, but an error in others. From what you are saying, it appears I meant candidate key instead of superkey. If there are no FDs, this means I am in BCNF? Does it also appear that I am in 5NF? I'll update my question with some more information about the business rules.
    – Lukazoid
    Oct 31, 2013 at 12:52
  • @Lukazoid -- study that link before providing data and rules. That example contains a table and dataset that can be considered BCNF or 6NF just by stating a business rule -- same table same data. Oct 31, 2013 at 13:14
  • Damir, you are right and my answer is wrong. I don't know what I was thinking of - BCNF maybe. Thanks anyway.
    – nvogel
    Nov 1, 2013 at 19:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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