6

I have the following tables:

Blogs { BlogName }
BlogPosts { BlogName, PostTitle }

Blog posts is modeling an entity and a relationship at the same time which is invalid according to 6nf (according to the third manifesto).

In 6nf, it would be:

Blogs { BlogName }
Posts { PostTitle }
BlogPosts { BlogName, PostTitle}

If I wanted to order blog posts by a sequence nbr (just an example), that would be another table

BlogPostsSorting { BlogName, PostTitle , SortOrder }

Do I have it correct?

philipxy
  • 14,867
  • 6
  • 39
  • 83
firebird
  • 3,461
  • 6
  • 34
  • 45
  • Your proposed 6NF schema assumes that no blog ever reuses a post title. – Jonathan Leffler Sep 25 '11 at 18:51
  • First, while Date and Darwen have a lot to say on 6NF, I don't believe TTM does. Second, note that although 6NF is always achievable it is not always desirable. See Darwen's An Introduction to Relational Database Theory (free download), 7.3 Assessment of 6NF decomposition, pp180-2. – onedaywhen Sep 26 '11 at 11:43

2 Answers2

6

What are the keys of your tables? Based on the column names I guess that the key of BlogPosts can only be {BlogName,PostTitle}. In that case BlogPosts is already in 6NF - it has no nonprime attributes and therefore cannot be nonloss decomposed. The Blogs relvar and Posts relvar would be redundant - you don't need them.

Blog posts is modeling an entity and a relationship at the same time which is invalid according to 6nf (according to the third manifesto)

Can you tell me where you think the Third Manifesto says that's invalid. I'm sure it doesn't but I'd like to know how you arrived at such a conclusion.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
nvogel
  • 24,981
  • 1
  • 44
  • 82
  • TTM doesn't have anything explicit to say about 6NF (why would it?) and Chris Date purposely avoids using the term 'entity' nowadays. – onedaywhen Sep 26 '11 at 11:25
  • 1
    Although not relating to normalization, there would be a practical design problem if we eliminated the Blogs relvar: we wouldn't be able to register a blog until the first title had been decided upon. I think this is the point @Erwin Smout makes, assuming they are taking about *implied* constraints. – onedaywhen Sep 26 '11 at 11:34
4

sqlvogel is correct in this answer.

Except for this little detail: whether Blogs is redundant or not depends on whether you want/need to enforce a constraint to the effect that all Blogs tuples must have at least one corresponding BlogPost tuple. You didn't state anything to make that clear.

The same holds for your third relvar Posts, except that in this case it is highly unlikely that it could be valid for a PostTitle to exist, without it appearing as the title of at least one BlogPost.

Whether you need the SortingOrder relvar as an extra one depends on whether or not there can be BlogPosts for which no sorting order is needed. If there cannot, then your SortingOrder relvar simply replaces BlogPosts. If there can, then you can have the two relvars; or alternatively you can still just have the SortingOrder relvar, and hack your way through the case of posts without ordering by using a dummy value (e.g., always -1).

Community
  • 1
  • 1
Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
  • BlogPostsSorting { BlogName, PostTitle , SortOrder }. If that's a key {BlogName, PostTitle} plus a single non-key attribute {SortOrder}, then it is 6NF. (Oh wait, were you referring to the effect of a possible second key {BlogName, SortOrder} ??? I don't think this affects anything. The relvar is still not nonloss decomposable.) – Erwin Smout Sep 26 '11 at 19:27
  • The comment that this comment was a response to seems to have disappeared. – Erwin Smout Sep 27 '11 at 16:36