Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
thanks very much! –  user1873604 Jul 3 '13 at 14:59
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
    
Probably, but 3NF is not about partial dependencies :) –  Andomar Jul 3 '13 at 15:20
    
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
add comment

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.

share|improve this answer
    
got it. thanks very much! –  user1873604 Jul 3 '13 at 14:59
    
In fact for reporting that is totally normal - not normalizing is standard for data warehouses. –  TomTom Jul 3 '13 at 15:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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