Support - Normlisation for Database Design

[Msor88]

Hi Guys,



First time poster! - I've been working with Excel for a while and feel like I've reached the limit of what I'm able to do with it. I've dabbled in Access before and I have a basic understanding of what I'm doing but seem to be struggling when designing the database ( and I know how critical this stage can be )

I've taken some time to watch the videos suggested by Orange in other threads and look at potential templates but after trying to follow the guidance I seem to be getting stuck around 2NF when I try to carry out normalization. So I've unfortunately resorted to posting my progress so far in the hope that someone can walk me through the rest of the way

Background
Within my current role I must check and record cases that colleagues work deciding if the decision made on their case was correct/incorrect - colleagues can have anywhere between 10 - 200 cases checked per day. Ideally I'll need to be able to pull reports breaking these scores down in the following ways;

- Employee Average as a percent of cases Passed V Fails - (Daily, Weekly, Rolling Weeks and Monthly)
- Process Average as a percent of cases Passed V Fails - (Daily, Weekly, Rolling Weeks and Monthly)
- Team Average as a percent of cases Passed V Fails - (Daily, Weekly, Rolling Weeks and Monthly)
- Department Average as a percent of cases Passed V Fails - (Daily, Weekly, Rolling Weeks and Monthly)

Progress so Far
So I started by looking at the paper form that is completed currently with the following details;

Header
- Colleagues ID
- Name
- Surname
- Team
- Process Checked

Body
- Date of Checks
- Date case was worked (almost always the same date
- Case #
- Pass or Fail Decision
- Notes for the check

So when trying to design a database I started by chucking everything in one Entity (obviously not going to work but in terms of theory I wanted to go from square 1)

Entity - Quality Check #
Quality Check # - PK
Colleague ID
Name
Surname
Team
Process
Case #
Date Worked
Date Checked
Decision
Notes

Obviously this is going to fail at 1NF due to repeating elements! Which leads me to this;

Entity - Quality Check #	Entity - Quality Check # // Case #
Quality Check # - PK	Quality Check # // Case # - PK
Colleague ID	Date Worked
Name	Date Checked
Surname	Decision
Team	Notes
Process

Now from here is honestly where I start to struggle - In the examples in the videos I need to look at creating a new element to remove partial dependencies but I can't quite decide or work out, which out of these are partial dependencies and I've obviously not looked at 3NF before going past this =\

Anyway - Any support on this would be greatly appreciated and thanks for taking the time to have a read!

Cheers,
Mike

[June7]

What are the relationships?

Colleagues can have multiple cases?

Each case will be assigned to one employee?

What are the Team and Process fields for?

Each case will have one review?

[burrina]

Here are a few links for you to consider.
http://www.databaseanswers.org/data_models/
http://www.access-diva.com/d1.html
http://access.mvps.org/access/tencommandments.htm
http://www.allenbrowne.com/AppIssueBadWord.html
http://www.accessmvp.com/strive4peace/

HTH

[Msor88]

What are the relationships?

Colleagues can have multiple cases?

Each case will be assigned to one employee?

What are the Team and Process fields for?

Each case will have one review?

Oh wow, quick replies. Thanks for the interest guys

So I'll try to answer a couple of your questions June7.

- I've not looked at the relationships yet, if I understand the theory (and obviously tell me if I can do it another way) I would look at ERD after I've completed normalization?
- A 'case' is assigned to one employee. However a case may come through our department more than once, so it may (be it rarely) be handled/assigned to more than one employee but never at the same time.

- Teams and processes? I'm best giving an example to try and explain.
We have 'Team 1' and 'Team 2'. Team 1 will 'Print and Scan work' while Team 2 Pre-checks work (2 different processes for 2 different Teams). On occasions where there is sickness or work volumes change a colleague from Team 1 will carry out Team 2's process. When this happens I will still need an average score for Team 1 including this employees work as well as a score Team 2's process taking into account the work the employee has done to support them. (I hope that all made sense)

- Edit; yes colleagues can have 100's of cases.

- Edit; Yes, each case will have one review (unless on the rare occasion that the case comes back through the department is worked by a different employee, in which case it would be checked again)

Burrina, thanks for the links. - I'm adding them to my 'long' list of reading ^_^

Cheers,
Mike

[June7]

IMHO, determining data relationships is part of the normalization analysis. ERD is a graphical representation of that analysis.

Rarely or not, should you design for the eventuality?

Individual employees, not teams, do reviews?

If Team1EmpA assists Team2, you need that time averaged into both teams data? Sounds a Process average and a Team average.

[Msor88]

I can have a look at the relationships now then and see how far I can get

So the answer is rarely - The ideal is to design the database with this eventuality in place (I feel like this might be issue later down the line due to what I decide to use as PK and don't want to have to come back to this stage to fix it)
Yes, Individual employee will review a case.
Yes - A Process Average & a Team Average would be required. (unfortunately this is the only reason I've got these fields at all)

Burrina - http://www.databaseanswers.org/data_models/ was particularly useful (loads of examples to look at) although I couldn't find anything to quite match what I was after & I had a good chuckle at the 10 commandments, all sound advice!

Thanks Again -

[burrina]

Does this give you any ideas?

[Msor88]

That's along the right lines Burrina, if anything it's actually more complex that this database needs to be considering the exampleit links to related cases, calls & history, etc.

I'm still struggling with relationships but am going to do a bit more reading tonight, so wish me luck! - Any suggestions or advice for getting it to 3rd nf in the mean time are always welcome

Cheers,
Mike

[June7]

Should you even worry about 3NF?

It is a balancing act between normalization and ease of data entry/output. At some point you might scream "ENOUGH ALREADY!"