Database Design Is this necessary for 3NF?

Question

Here is the database design I am working on for a voting based website (overpowered.info). http://i57.tinypic.com/2v9925s.png I am interested in the interaction between user and security. I need this database to be in the 3NF and this is the first time I am normalizing a database through the normalization process. At first I had the security answer and the security question within the user. However, the answer is related to the question and dependent on both the question and the user. Is it still in the 3NF if I have USER_ANSWER and QUESTION_ID within the USER entity and get rid of security all together? I know that security is 1 to 1 to user and that should be avoided but I am ultimately unsure at this point.

Answer 1

It can't be in 3nf. For one thing, the answer is functionally dependent on question_id which is not any part of the user key. Here is one suggestion:

I'm assuming the Questions table contains a list of stock questions the user selects from, then supplies the answer to the one selected. So Security implements a one-to-many relation between User and Questions. The User_ID field is both a FK to the User table and a PK so no user may be listed more than once. For each user, the relevant question is also listed as well as this user's answer to that question.

Notice I've also moved the Password field to a separate table (along with a hash seed -- you are storing only the final hash not the text of the password, right?). Think of this as "Security Normal Form" (SNF) where sensitive data (which can include account numbers, SSN, etc.) is placed in a separate table. Access to such tables can then be severely restricted. The sensitive data (which includes the questions and answers) would then not be compromised if the user table itself was ever hacked. No, SNF will not be found in any tutorial about normalization, but I've used it for years and it makes for better security. It's a lot easier to limit access to entire tables than specific fields within tables.

Answer 2

Yes, it'll still be in 3NF. In fact, it'll fix a design flaw with security, in which you couldn't distinguish answers for different users who answered the same question.