Db design review

  • Thread starter Thread starter Stephanie
  • Start date Start date
S

Stephanie

Hi. I’m trying to create a new db. And when I think of the trauma I had
initially with my last db, I realize I could really use some input during the
design phase. I often have my many-to-one backwards…

There are 5 business units. Each BU has multiple Departments.

There are 6 or so Risk Drivers. Each risk driver has multiple risks. Each
risk has
multiple “risk scoring factorsâ€. More that one factor can be selected for
each risk. Each factor has an associate score. If the score is 1 or 2, the
departments must provide comments.

Every month the department must complete an assessment of the risk drivers
and associated risks, based on the scoring factors. All departments will use
the same risks/factors.

I’d like one form that has 6 sections (1 for each risk driver): Each
associated risk will be shown in each section, with a list box (not sure- can
you multi-select from a list?). The score will be populated and the comment
field available for 1,2 score.

What would be the record source for this form?

I’d like a status field so that I could tell if the departments have
finished the assessment for the month.

Every month each business unit will roll up assessments from each department
into a holistic view.

tblBusinessUnit
BusinessUnitID (primary)
BusinessUnitName
DepartmentID (foreign)

tblDepartment
DepartmentID (primary)
DepartmentName

tblRiskDriver
RiskDriverID (primary)
RiskName
RiskID (foreign)

tblRisk
RiskID (primary)
RiskName
RiskFactorID (foreign)

tblRiskFactor
RiskFactorID (primary)
RiskFactorName
RiskScoreID (foreign)
Comment12Score (is this where the comment field would go for a score of 1,
2?)

tblRiskScore
RiskScoreID (primary)
RiskScore

Not sure how to handle month, year…
tblMonth
MonthID (primary)
MonthName
Stauts (is this where the department would enter a status, which would
indicate if the assessment had been completed?)

tblYear
YearID
YearName

Thanks for reading this post- I appreciate your help in making sure the
design is solid before I begin.

Cheers,
Stephanie
 
Thanks for the reply!

I think the "LastAssessmentDate" as fields in tblDepartment and
rblBusinessUnit would work. So if I had a "submit" assessment button on a
form, could I write the today's date to the LastAssessmentDate field?

I thought I might be missing a linkage! Here's an explanation so you can see
the process:

The risk drivers are the same for each department (people, process,
systems...).

The risks are the same for each department (insufficient training, reliance
on manual processes, system infrastructure breakdown...).

The risk scoring factors are the same for each department (employees are not
current according to training schedule, there has been an recordable event
during the month due to a manual error, the system has been down for more
than 24 hours in the last month...).

The risk score is based on the severity of the factor (a reportable event is
"big"- a score of 2- while employee training is a lesser bad event - a score
of 1-). I originally wanted a scoring table in case I wanted to change the
scoring (say, a scale of 1-3), but I can just add a Score field to
tblRiskFactor.

So all that said- I'm not sure how get the table structure that will allow
me to score the risk factors for each of the risks in each of the risk
drivers for each department monthly. I'm hoping the explanation will give you
enough information to make some suggestions.

Cheers,
Stephanie
 
Hi Stephanie,

You are at the start of the very beginning of a long, steep learning
curve. You are to be congratulated that you already have at least one
application out there working for you. Access provides wonderful set
of capabilities for getting things done. You seem to be trying to
beat on it to give you the results you want without really
understanding either its capabilities (immense and growing) or its
limitations.(many - but fortunately, not very many in the area of its
goals).

In the kindest of words about your design, "It's not very good and
will not serve as the basis for a successful application". Hang in
there, life isn't over ...

Your description of the real world entities and the processes to be
modeled is excellent and should serve as an important tool in creating
a very good application.

Some observations:

One to Many Relationships; the Child (many side) always identifies
the Parent (the one side) not the other way around - as you have shown
it
tblBusinessUnit
BusinessUnitID (primary)
BusinessUnitName
DepartmentID (foreign)

tblDepartment
DepartmentID (primary)
DepartmentName
Click to expand...

should be

tblBusinessUnit
BusinessUnitID (primary)
BusinessUnitName

tblBusUnitDept <-- This is a junction table
BusUnitDeptID (primary) <-- Some folks don't use this
BusinessUnitID (Foreign)
DepartmentID (Foreign) <-- tblDepartment has *all* department
names
BusUnitDeptNotes (memo) <-- notes about just this BUD instance

Lookup tables are simply regular tables that are used to hold lists of
things: colors, sizes, Risks, RiskFactors, etc. While they can model
as being the One side of a One-To=Many relationship and you can sort
and search on that relationship, their real purpose is to hold values
that can be placed in other tables. Just about every application uses
lookup tables. [Don't confuse them with "Lookup Fields" in tables -
those are to be avoided at all costs].

Do not create tables solely for any date function. Use date fields
appropriately in individual records where they make sense. For
example; the creation of a record could have a date field: when the
record of an evaluation is created, when a comment in reply was
created. (both of those examples would also likely benefit from
having the author identified).

Your request for "review" segues into several direct or implied
questions about several things that, in effect, require someone to do
your design for you. Not fair! The responses in these newsgroups are
from unpaid volunteers. The understood rationale is that we help a
developer at any level over a specific technical hurdle. The is an
implicit assumption that people posting their issues will do all that
they can to help themselves as well as any help they get here. My
impression is that you are someone who will do that. The courteous
rule of thumb is "one technical or global issue per thread".

Please try digesting the above and just some of what follows and try
tweaking your design and see how it goes.


A couple of newsgroups I always recommend for Access newbies are:

microsoft.public.gettingstarted
microsoft.public.tablesdesign

A list of priceless Access resources I cribbed from the frequent posts
of John Vinson is below

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

HTH
 
Thank you for your help.

PieterLinden via AccessMonster.com said:
Stephanie said:
Hi. I’m trying to create a new db. And when I think of the trauma I had
initially with my last db, I realize I could really use some input during the
design phase. I often have my many-to-one backwards…

There are 5 business units. Each BU has multiple Departments.

There are 6 or so Risk Drivers. Each risk driver has multiple risks. Each
risk has
multiple “risk scoring factorsâ€. More that one factor can be selected for
each risk. Each factor has an associate score. If the score is 1 or 2, the
departments must provide comments.

Every month the department must complete an assessment of the risk drivers
and associated risks, based on the scoring factors. All departments will use
the same risks/factors.

I’d like one form that has 6 sections (1 for each risk driver): Each
associated risk will be shown in each section, with a list box (not sure- can
you multi-select from a list?). The score will be populated and the comment
field available for 1,2 score.

What would be the record source for this form?

I’d like a status field so that I could tell if the departments have
finished the assessment for the month.

Every month each business unit will roll up assessments from each department
into a holistic view.

tblBusinessUnit
BusinessUnitID (primary)
BusinessUnitName
DepartmentID (foreign)

tblDepartment
DepartmentID (primary)
DepartmentName

tblRiskDriver
RiskDriverID (primary)
RiskName
RiskID (foreign)

tblRisk
RiskID (primary)
RiskName
RiskFactorID (foreign)

tblRiskFactor
RiskFactorID (primary)
RiskFactorName
RiskScoreID (foreign)
Comment12Score (is this where the comment field would go for a score of 1,
2?)

tblRiskScore
RiskScoreID (primary)
RiskScore

Not sure how to handle month, year…
tblMonth
MonthID (primary)
MonthName
Stauts (is this where the department would enter a status, which would
indicate if the assessment had been completed?)

tblYear
YearID
YearName
Click to expand...
Stephanie,
I would start with a written description of what you are trying to model.
Generally, if you get the core attributes (facts you want to store about each
"thing" in your model), you can expand on it later. That said, I would
recommend turning off the computer and drawing this out on paper (or
notecards), because getting the design right is absolutely critical. A
properly designed database will make getting answers (writing queries is a
good test) - don't bother with forms until your underlying tables are
structured properly. If the tables are right, the rest will flow easily.

When they made us do this in school, we wrote out really simple sentences.

Each [Subject] can [verb] one | many [Object]. and then test
Each [Object] can [verb] one | many [Subject].

for example:
Each [Business Unit] can consist of one or more [Departments].
Each [Department] can consist of one or more [Business Units].

If only one of the two statements in the pair is true, it's a One-to-many
relationship.
If both can be true, then it's a many-to-many. Then you can just draw the
diagram of your database on paper. Then test that to see if you can answer
all the questions you can think of. If that works okay, build away. If it
doesn't, then your design is wrong. Easier to fix it now than later!!!
Click to expand...
 
Thanks for taking the time to reply. I appreciate the link references.

Larry Daugherty said:
Hi Stephanie,

You are at the start of the very beginning of a long, steep learning
curve. You are to be congratulated that you already have at least one
application out there working for you. Access provides wonderful set
of capabilities for getting things done. You seem to be trying to
beat on it to give you the results you want without really
understanding either its capabilities (immense and growing) or its
limitations.(many - but fortunately, not very many in the area of its
goals).

In the kindest of words about your design, "It's not very good and
will not serve as the basis for a successful application". Hang in
there, life isn't over ...

Your description of the real world entities and the processes to be
modeled is excellent and should serve as an important tool in creating
a very good application.

Some observations:

One to Many Relationships; the Child (many side) always identifies
the Parent (the one side) not the other way around - as you have shown
it
tblBusinessUnit
BusinessUnitID (primary)
BusinessUnitName
DepartmentID (foreign)

tblDepartment
DepartmentID (primary)
DepartmentName
Click to expand...

should be

tblBusinessUnit
BusinessUnitID (primary)
BusinessUnitName

tblBusUnitDept <-- This is a junction table
BusUnitDeptID (primary) <-- Some folks don't use this
BusinessUnitID (Foreign)
DepartmentID (Foreign) <-- tblDepartment has *all* department
names
BusUnitDeptNotes (memo) <-- notes about just this BUD instance

Lookup tables are simply regular tables that are used to hold lists of
things: colors, sizes, Risks, RiskFactors, etc. While they can model
as being the One side of a One-To=Many relationship and you can sort
and search on that relationship, their real purpose is to hold values
that can be placed in other tables. Just about every application uses
lookup tables. [Don't confuse them with "Lookup Fields" in tables -
those are to be avoided at all costs].

Do not create tables solely for any date function. Use date fields
appropriately in individual records where they make sense. For
example; the creation of a record could have a date field: when the
record of an evaluation is created, when a comment in reply was
created. (both of those examples would also likely benefit from
having the author identified).

Your request for "review" segues into several direct or implied
questions about several things that, in effect, require someone to do
your design for you. Not fair! The responses in these newsgroups are
from unpaid volunteers. The understood rationale is that we help a
developer at any level over a specific technical hurdle. The is an
implicit assumption that people posting their issues will do all that
they can to help themselves as well as any help they get here. My
impression is that you are someone who will do that. The courteous
rule of thumb is "one technical or global issue per thread".

Please try digesting the above and just some of what follows and try
tweaking your design and see how it goes.


A couple of newsgroups I always recommend for Access newbies are:

microsoft.public.gettingstarted
microsoft.public.tablesdesign

A list of priceless Access resources I cribbed from the frequent posts
of John Vinson is below

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

HTH
--
-Larry-
--

Stephanie said:
Hi. I'm trying to create a new db. And when I think of the trauma I had
initially with my last db, I realize I could really use some input during the
design phase. I often have my many-to-one backwards.

There are 5 business units. Each BU has multiple Departments.

There are 6 or so Risk Drivers. Each risk driver has multiple risks. Each
risk has
multiple "risk scoring factors". More that one factor can be selected for
each risk. Each factor has an associate score. If the score is 1 or 2, the
departments must provide comments.

Every month the department must complete an assessment of the risk drivers
and associated risks, based on the scoring factors. All departments will use
the same risks/factors.

I'd like one form that has 6 sections (1 for each risk driver): Each
associated risk will be shown in each section, with a list box (not sure- can
you multi-select from a list?). The score will be populated and the comment
field available for 1,2 score.

What would be the record source for this form?

I'd like a status field so that I could tell if the departments have
finished the assessment for the month.

Every month each business unit will roll up assessments from each department
into a holistic view.

tblBusinessUnit
BusinessUnitID (primary)
BusinessUnitName
DepartmentID (foreign)

tblDepartment
DepartmentID (primary)
DepartmentName

tblRiskDriver
RiskDriverID (primary)
RiskName
RiskID (foreign)

tblRisk
RiskID (primary)
RiskName
RiskFactorID (foreign)

tblRiskFactor
RiskFactorID (primary)
RiskFactorName
RiskScoreID (foreign)
Comment12Score (is this where the comment field would go for a score of 1,
2?)

tblRiskScore
RiskScoreID (primary)
RiskScore

Not sure how to handle month, year.
tblMonth
MonthID (primary)
MonthName
Stauts (is this where the department would enter a status, which would
indicate if the assessment had been completed?)

tblYear
YearID
YearName

Thanks for reading this post- I appreciate your help in making sure the
design is solid before I begin.

Cheers,
Stephanie
Click to expand...
Click to expand...
 
Steve said:
You are the ignorant &%#@@% !!!!
Click to expand...

I'm not sure what a "&%#@@%" is but you have no basis for such a remark,
it is merely your worthless opinion. If you can cite any facts to support
your remark then please do share them here, either put up or shut up. I
certainly don't have one of these:

http://home.tiscali.nl/arracom/whoissteve.html

and I'd be thoroughly ashamed if I did.
 
Steve said:
"LastAssessmentDate" in TblDepartment and in TblBusinessUnit is incorrect!
You have a complex process to model! I provide help with Access
applications for a reasonabe fee. I can help you design the tables you
need. My fee would be very modest. Contact me if you are interested.
Ignore any stalkers that may respond.

Steve
(e-mail address removed)
Click to expand...

stevie, you are the only stalker around here. For years you are the ONLY one
has been ignoring the rules and STALKING posters.


These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)
Yes, you are right but a database is the correct tool to use not a
spreadsheet.
Click to expand...


Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...




John... Visio MVP
 
Steve said:
Also add;

Ignore any ignorant &%#@@% that may respond.
Click to expand...


Nice language for someone who supposedly is married to a Sunday school
teacher. Did you sleep through the session when they talked about the Good
Samaritan?

John... Visio MVP
 
Back
Top