Advise with normalisation

[tigersden]

I have been given a flat, only 1 table Access DB to work with. Please could anyone let me know if they my normalisation of this is OK. I have joined all the tables together using tblAssessment at the bottom of the list.
Thanks in advance.

tblRisk
RiskRef PK
RiskDesc
ReviewFreq

tblDept
DeptRef PK
Department

tblProcess
ProcessRef PK
ProcessDesc

tblAssessor
AssessorRef PK
AssessorNam

tblTask
TaskRef PK
Task /Activity

tblPersonAtRisk
PersonRef PK
PersonRef

tblControlMeas
ControlMeasRef PK
ControlMeasure

tblComments
CommentsRef PK
Comments

tblReview
ReviewRef PK
ReviewDT
Severity
Probability

tblMethodUsed
MethodRef PK
MethodDesc

tblAssesment
AssessRef PK
RiskRef FK_tblRisk
DeptRef FK_Dept
ProcessRef FK_tblProcess
AssessorRef FK_tblAssessor
TaskRef FK_tblTask
PersonRef FK_tblPersonAtRisk
ControlMeasRef FK_ControlMeas
CommentsRef FK_tblComments
ReviewRef FK_tblReview
MethodRef FK_tblMethodUsed

 

[TonyJollans]

Hi tigersden,

It is impossible to say for sure but it seems very unlikely to me that this design is good for any real-world situation.

Eight of your tables are just lookups with a key and a description. The Risk table has a review frequency (but no relationship to a review). The Review table has a couple of attributes (of no relevance to anything but the review?) and then the list of foreign keys in the Assessment.

When you say I have been given a flat, only 1 table Access DB do you mean you are starting from a flat file? You stll have, essentially, a flat file.

You should try and design around your requirements, not your data. What are your requirements? How are the (real world) elements related to each other?

Incidentally this would have been better in the Microsoft: Access Tables and Relationships forum as it has nothing to do with Forms.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[willir]

tigersden

Tony has made some good observations in his post.
- Assessment table uses 10 foreign keys to "tie" data together.
- Many of your linked tables are used solely to control selected option -- Tony refers to this as "lookups". This is fine, especially when you want to analyze your data later in terms of methods used etc. But you can do the same with a spreadsheet, value list, etc.
- Your relations appear to lack depth.

It helps to understand the purpose of a database -- what are the expected outcomes, what information do you need to report. By knowing what information and expected outcomes are required will lead to determine what data is needed.

For example, suppose you want to document the assessment of a child to determine the type of program they should follow. You would have interviews -- this implies, interviewers and interviewees, interview dates, details and outcomes for each inteyrview. This also implies you would have a one-to-many relationship. One child being assessed, many interviews.

Richard

 

[tigersden]

Thanks both for replying.
I have taken on board your suggestions.