4

I've got a photo review type of application, where the customer wants to have an option for the user to flag issues with the photos. So, for instance, a photo could be tagged as "fuzzy," "grainy," or "other." If "Other" is selected, a brief text description is required.

I am thinking I should create one table with all the issue types. Another table, will have foreign key relationships to the photo id, and the issue type id. I am thinking this table should also have a text type column for the "other" text if "other" is selected, otherwise that column will be null.

My dilemma, is, will this be a violation of normalization rules? If the "Other" text column is not null, then the assumption can be made that the issue type id will always be the one for "Other." So, that would be a transitive dependency, right? Any ideas on how to implement this without violating 3rd normal form?

3

If the column OtherText is null, you do not know the value of the column IssueType. It could be fuzzy or grainy. So IssueType is not dependent on OtherText.

Your design is not a 3NF violation.

  • 1
    But when the IssueType is anything (but "other") then the OtherText column is null. So it is a (partial) dependency. – ypercubeᵀᴹ Jul 3 '13 at 15:09
  • 1
    Probably, but 3NF is not about partial dependencies :) – Andomar Jul 3 '13 at 15:20
  • 2
    No, I agree on that. Depending on how one sees nullability, the table is either violating 1NF (having a nullable column) or (allowing nulls) it is ok with 1NF and 3NF - and only violates DKNF. – ypercubeᵀᴹ Jul 3 '13 at 15:24
2

I wouldn't worry to much about the "Normal Forms". They are excellent recommendations and should be followed where appropriate. But only where appropriate. In this particular case you don't have an issue with it anyway as @Andomar said, but I can think of several times when I have violated various normal forms in order to improve performance for queries or reporting.

  • In fact for reporting that is totally normal - not normalizing is standard for data warehouses. – TomTom Jul 3 '13 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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