design - sixth normal form

1.3k views Asked by At

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?

2

There are 2 answers

2
Erwin Smout On BEST ANSWER

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

2
nvogel On

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.