Normalization Question 1

[jacobpressures]

Hi guys! I'm creating a testing application. One of my goals is to store the employee info, questions, number of test taken, etc for each test an employee takes. The tests are all the same except that the questions will be selected from a pool.

Questions are grouped into Categories. Each Category has one or more goals. Some questions may have a diagram.

Below are 10 tables. I think they are pretty normalized. I just want to know if you think this is a good design or not. I've attached a picture of an ER-Diagram i created in MS Access also.

I also have a few questions about some derived fields i would like to ask later.

Thanks!

Employee
--EmpID*
--FName
--LName
--Position Employee’s Title or Position
--Center Employee’s Main Location

Table Description:
Stores vital information about Employees


Center
--CenterID*
--CenterName Name of each Franklin Center

Table Description:
Stores all my company's centers


Test
--TestID* Identifies all employee testing information
--EmpID

Table Description:
Used to identify all employee tests using a unique identifier (TestID) that can be used to identify all questions presented to employee.


Category
--CatID* Primary Key; Unique Identifier for all categories
--CategoryName
--CategoryPurpose Desc. of the purpose of each testing Category

Table Description:
Provides each testing category with a unique identifier and name. It also stores a description of the purpose of the testing category.


Goal
--GoalID*
--CatID
--GoalDescription Desc. of what each employee should learn after completing a Category.

Table Description:
Stores all of the goals for each Category (or testing Objective).


Question
--QID*
--CatID Foreign Key from Category table. Used to identify which question belongs to which Category.
--Question Description of question

Table Description:
Provides a pool of questions for each Category.


Answer
--AnswerID*
--QID
--Answer Possible answer to a particular question
--Correct Boolean value describing whether Answer is a correct choice.

Table Description:
Stores all the possible answers to a particular question.


Diagram
--DiagramID*
--QID
--FileName Name of diagram file; used to find file on disk.
--DiagramDescription Description of file

Table Description:
Stores information diagrams used on the test.


Completed_Category
--CCID*
--TestID Foreign Key from Test table; used to identify which set of Categories belong to each TestID.
--CatID Foreign Key from Category table; used to identify tested or completed Category.
--StartTime Stores the date and time testing of Category began.
--FinishTime Stores the date and time testing of Category was completed.

Table Description:
Used to store all Categories completed by user. Ties completed Category to a specific TestID, allowing for retesting.


Completed_Question
--CQID* Primary Key; Uniquely identifies completed questions for each TestID
--CCID Foreign Key from Completed_Category table. Identifies a Category and TestID to a completed or answered question.
--QID
--Correct Boolean value

Table Description:
Stores all the test questions for a particular TestID by Category while providing information indicating whether the question was correctly answered or not.

 

[jacobpressures]

I wanted to included an ER Diagram from Access so you can see the layout better but there appears to be no way to including an attachment.

 

[willir]

Jacob

Your design looks pretty impressive. You will obviously track a lot of info.

First, here is a link to a post I wrote a few months ago. (See May 20th post) It addresses a similar issue to your complex design. For example, categorizing questions.

http://www.tek-tips.com/viewthread.cfm?SQID=844594&SPID=705&page=1

...Moving on
It looks like you will be tracking some heavy duty data so I suggest you test your design. This may take a couple of days - but it is far better to make sure you are on the right track at the start than to find out you can not capture your required data after you have 1000's of records.

FIRST, decide what outcomes you are looking for -- seem kind of funny documenting specifications for reports and forms, but the information you want drives the design.

Second, I like to work out the data flow on paper - easier to use an eraser than redesign stuff in Access.

Next, design the database and create the relationships.

Then, on a backup copy of your database, enter test data at the table level (you may want to hold off creating forms until you are confident with your design) NOTE: This is probably the only time I would ever in think of suggesting some enter data at the table level. You have many-to-many relationships here so be careful.

Once you have some test data, design and test your queries. Make sure you can meet the outcomes you defined at the start. Of course, you will have to tweak this or that -- par for the course.

There is one interesting link that I will send you later tonight (when I find it).

Good luck on this project.
Richard

 

[willir]

As promised...

This is one of the better Access sites hosted by Dev Ashish & Arvin Meyer...

http://www.mvps.org/access/tencommandments.htm

Aside from very sage advice on Access, it has some pretty good tools too.

 

[jacobpressures]

I must say thanks Willir for your hard work in the previous thread. it was great information. It will help me in more ways than one.

I printed out the comment and will read over it again and highlight sections that apply to me and will ask more questions if i need to. Before hand i will try to see if i can figure it out on my own so it may take some time to get back to you.

I see some good fields taht i can add such as the LastModified Date field for my questions. that was a good idea i think.

Another thing is that i was planning, in the beginning, to add an Employee Satisfaction Survey to this exam, however, originally it wasn't going to be such a complex idea. I was just going to print out the results. But i see from your example in the other thread that this really isn't much different from what I'm already doing. There are issues though, but hopefully everything will be ok. It will certainly be a learning experience.

I also wasn't going to include a response table for employee answers but if i add the survey to it, this will be necessary. It is probably best anyway though i see no need of knowing what answer an employee chose for a particular question. With the survey this, will be necessary since the main focus will be the employee responses. Thanks again.

Here are some comments and questions about your post above in the meantime.

It looks like you will be tracking some heavy duty data so I suggest you test your design. This may take a couple of days - but it is far better to make sure you are on the right track at the start than to find out you can not capture your required data after you have 1000's of records.

FIRST, decide what outcomes you are looking for -- seem kind of funny documenting specifications for reports and forms, but the information you want drives the design.

I created a list of possible questions. it is not all of the questions but mostly some of the more complicated ones. Some include using derived fields also.

Basically i guess one question is, should i not worry so much about what i will query the DB for until after i have a basic design finished as long as the information is there? It seems hard to anticipate all the queries, and data combinations that will be used during the design phrase.

Second, I like to work out the data flow on paper - easier to use an eraser than redesign stuff in Access.

what do you mean here by data flow?

Again Thanks! I liked the Ten Commandments too by the way.

 

[willir]

Jacob

The data flow is sketching out the relationships between the tables. Something similar to the Relationship window but you can focus on each system / subsystem.

How do you generate a question?
Show the linkage from table to table.

How do you answer a question?
Show the linkage from table to table.

How do you analyze the results?

When you do this, you can actually pencil in some test data. By using data, it forces you to not to make assumptions.

By breaking the big picture into smaller subsystems, it makes the problem solving much easier.

Richard

 

[jacobpressures]

Thanks Willir!

Here are 2 questions from the information you provided. I've redone my DB schema also. the info was very helpful.

1)

The Answer table contains different types of pre-determined answers. Multiple Choice, T/F, and Numeric, which means that for a particular question, i would have to store 5 possible choices if the answers are numeric.

for example.

Rate the following questons.

Does your doctor provide a satisfactory answer to your questions? 1 2 3 4 5


For each question there will be 5 answer IDs. the 2 for T/F.

Is there a way to possibly put these choices in a table by themselves and reference them without being so redundant? It has been hard for me to come up with something logical. the easy thing of course would be to simply keep it the way it is.


2)
Even though i don't have any use for a SortOrder field currently, do you think i should include one just in case i need one in the future?

Thanks!

 

[willir]

Regarding the first issue...
Multiple variable types. Tough one - for a generic response, you do not know if the answer will be T/F, short answer, numeric, or even a date. A one answer field would be to use a type variant, but I thought this would make the anlysis more awkward. So I wnet with the multiple data types.

Sort may be important.
Imagine asking for all sorts of stuff, and in the middle, asking for the person's name. It gives you control on where and which questions to ask. For example, group all the T/F questions and then the short answer.

 

[jacobpressures]

Thanks! The purpose of DB design is to design for possible future use. So the sort order may be important. I will include it.

The problem with the Answers table is that i realized last night that it really is a M:M relationship. The current design works but it allows for quite a bit of redundancy. For a range of 1-5 or 1-10 rate the following 10 questions could get huge with only numbers in the Answer field. However, i wonder if breaking down the seemingly M:M relationship would be more problems than necessary.

IF i keep the table as it is, i feel as if there will be no major problem with converting the data from type text or variant to integer if necessary. the DB will be connected to a VB app that will retrieve and process the data.

What do you think about the M:M relationship? should i break it down? I don't know if there is a real need to know the type of question at this point, but as you know DBs requirements change and what you don't anticipate can be costly.
What about the datatype?
And Instead of M:M is that correctly notated as M:N?

Thanks very much and i hope i'm not wearing out my welcome. You have been helpful to me enough.