Is the data normalized correctly?

[dahkla]

Before I'm going to build a database in Access I have to normalize my data. My question is if this is done correctly:


My data (FAQ) consists of questions, answers,categories and versioning. Questions can be answered with text and/or image. The questions can be divided into multiple Categories.
Its kept which person(userID) changed the answer. (versioning)
The older versions of answers wont be saved when an answer is changed by a person.

bold = primary key
RG = repeating group

0NF:


FAQ(FAQid, question, ansText, ansImage, RG(Category),RG(userID,dateModified,revisionNR, comment))

1NF:
FAQ(FAQid,question, ansText, ansImage)
Categories(FAQid,CatName)
Versioning(FAQid,revisionNR, userID, dateModified, comment)

2NF: =1NF

3NF:
FAQ(FAQid, Qid, question )
Questions(Qid, ansText, ansImage)
Categories(FAQid,Category)
Versioning(FAQid,revisionNR, name, dateModified, comment)

I'm not quite sure if 3NV is good. I know that the rule of 3NV is that you have to eliminate the columns that are not fully dependent upon the primary key.

[June7]

Can each question be associated with multiple subjects (is this same as category)?

[dahkla]

^ Yes its the same as category.
I edit my first post

[dahkla]

Can I say (for 2NV )that name and modifiedDate only depends on the primary key FAQid and not revisionNr ?:
2NV:
FAQ( FAQid , question, ansText, ansImage)
Categories(FAQid,Category)
Versioning(FAQid, name , modifiedDate, VersioningID ) (new key added)
Division(FAQid, revisionNR)

[June7]

Was that a yes that each question can associate with multiple categories?

Do you want a table of categories to use as a source for combobox list?

If yes to both then consider:

tblCategories
CatID
CatName

tblQuestionCategories
FAQid
CatID


Name is a reserved word and should not use reserved words as field names. Also, probably should not save name into Versioning and instead save UserID. This means will need a table of Users.

[dahkla]

^ yes it can be associated with multiple categories.

You are right about using userID instead of name. I forgot a column named 'comment' for versioning . I changed my first post (new table image added).

Do you want a table of categories to use as a source for combobox list?

If yes to both then consider:

tblCategories
CatID
CatName

tblQuestionCategories
FAQid
CatID

But one table (Categories) is enough, right? Becuase there is only one item which matches the unique combination FAQid and Catnam:

Table Categories
FAQid | CatName
1 | General
1 | Cost
2 | Service
3 | General

And I have to use DISTINCT to use CatName for the comboBox

[June7]

Call it what you want but tblQuestionCategories makes sense to me. Then what about a table as lookup source for the categories - will there be more than 3 categories?

tblCategories

CatID	CatName
1	General
2	Service
3	Cost
4	something1
5	something2
6	something3
7	something4

Since the category names are very short, could just save them into tblQuestionCategories instead of CatID - this will simplify queries.

[dahkla]

is it correct to say that 2NF = 1NF? Or should I make another table which contains userID, modifiedDate and comment because they only depend on FAQid?

[June7]

I am not formally trained so identifying 1NF, 2NF, 3NF is not routine for me, I know the concepts but never really applied in my design efforts.

It is a balancing act between normalization and ease of data entry/output. Normalize until it hurts.

I have a db that ignores 'rules' but it suits our requirements to achieve the desired goal, which is the generation of laboratory reports. We do very little statistical analysis with the data, just distribute the results. So the db is designed to make that output as easy as possible.