1

I'm trying to design a database. I have a table as follows:

Tag     Post     File
A        a         1
A        a         2
A        a         3
A        b         4
B        a         1
B        b         1
B        c         1
B        d         1

Each post may have more than one tag and each file may have more than one tag. Aside from the tags the posts and files are unrelated. Obviously just from looking at the table there are going to be redundancy issues for example the post-tag pair A-a is repeated 3 times unnecessarily, same with the post-file pair B-1. However I've looked at the definitions for 1NF up to 4NF and it seems to be in all of them.

What did I miss?

I thought that if a table had redundancies then it's not normalized. But it does seem to be normalized up to 4NF. So I must have gone wrong somewhere.

user2108462
  • 855
  • 7
  • 23
  • You don't say whether a given tag can have more than one post or file. Also "unrelated" doesn't mean anything in particular. Nor does "redundancy". Nor is it "obvious" that there are any NF can remove. If you are trying to include that tag->>post|file, ie that this table is always the natural join of its projections on {tag, post} & {tag,file}, then that contradicts your example data. No non-trivial MVDs hold. You need to find out what FDs & MVDs (or binary JDs) are & say which FDs hold & don't & which MVDs/JDs hold & don't (or what lossless decompositions there are). Informal is inadequate. – philipxy Jul 21 '20 at 21:04
  • Re "is this right": Show the steps of your work following your reference/textbook, with justification--not all terms/notations are standard & we don't know exactly what algorithm/method you are following & we want to check your work but not redo it & we need your choices when an process allows them & otherwise we can't tell you where you went right or wrong & we don't want to rewrite your textbook. Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. If you're not sure it's right, ask 1 specific researched non-duplicate question re where you got stuck. – philipxy Jul 21 '20 at 21:04

1 Answers1

2

Based on your description you should break down that table into 2 tables to model the One-To-Many relationship between "Post and Tag", and "File and Tag"

POST   TAG
a       A
b       A
a       B
b       B
c       B
d       B


FILE   TAG
1       a
2       a
3       a
4       b
1       b
1       c
1       d

Take a look at the first example here:

http://en.wikipedia.org/wiki/Fourth_normal_form

It is typically the same case you are describing in your question.

Here's a useful question too:

Database Normalization

Community
  • 1
  • 1
Rami
  • 7,162
  • 1
  • 22
  • 19
  • So...is my table normalized? Why or why not? Is there a rule that says I should split my table into two? Wouldn't that double the amount of records? – user2108462 Mar 21 '14 at 09:04
  • No, your table is not normalized according to the Fourth Normal Form definition because as you said POST and FILE are unrelated i.e. independent on each other, and yet exist in the same table which violates the only requirement for Fourth Normal Form and creates redundancy as you noted. Splitting the table into 2 solves this problem since each column in each table depends on the other. – Rami Mar 21 '14 at 12:01
  • 1
    I see, it's a multivalued dependency! – user2108462 Mar 21 '14 at 21:54
  • @user2108462 & Rami In the question tags are A,B,... not a,b,.... So the 2nd table doesn't make sense. For further problems see my comment on the question. – philipxy Jul 21 '20 at 21:06