Link Multiple Criteria

J

JD McLeod

I am creating a database for risk management. In this database, users will
input risks and the controls that mitigate those risks. The problem I am
having in designing the table is that one control may mitigate more than one
risk. I envision a table for the risks which contains all of the details
such as risk type, risk category etc. I then thought I would have a table
for the controls and related data such as performance frequency, person
responsible, etc. Where I am stuck is on how to “assign†(link) one of the
controls to one or more risks. I am just beginning to look at the table
design, so any help or thoughts on how to accomplish this would be
appreciated.
 
B

Beetle

It sound like you have, essentially, a many-to-many
relationship between Risks and Controls, in which case
you need a third table to manage it. Example;

tblRisks
******
RiskID (Primary Key)
RiskName
other attributes of each Risk

tblControls
********
ControlID (PK)
ControlName
PersonID (Foreign Key to tblPersons)
other attributes of each Control

tblRiskControls
***********
RiskID (FK to tblRisks)
ControlID (FK to tblControls)
any attributes that are specific to the relationship

(RiskID and ControlID would be a combined PK for the above table)

tblPersons
********
PersonID(PK)
FirstName
LastName
Title
other attributes of each person
 
S

Steve

It sounds like you need multiple tables:

TblRiskType
RiskTypeID
RiskType

TblRiskCategory
RiskCategoryID
RiskCategory

TblRisk
RiskID
RiskTypeID
RiskCategoryID
RiskDesc

TblPerson
PersonID
PersonFirstName
PersonLastName

TblRiskControl
RiskControlID
RiskControlDesc

TblPerformanceFrequency
PerformanceFrequencyID
PerformanceFrequency

TblRiskMitigation
RiskMitigationID
RiskID
PersonID
RiskControlID
PerformanceFrequencyID


Steve
(e-mail address removed)
 
J

JD McLeod

Thank Sean. The table structure you described makes sense. Can you
elaborate on the mechanics of how i would populate the third table to "create
the link"? Would i use a form that pulls data from the other two tables? I
looked up this topic in Access help, but it didn't really explaon how the
third table would work. thanks for your help.
 
B

Beetle

For example, if you wanted to enter/view a Risk and
then assign all the associated Controls, you would create
a main form based on tblRisks with a sub form based
on tblRiskControls (the junction table) using RiskID as
the Master/Child link. In the sub form you would use a
combo box bound to ControlID in order to enter the
Controls that relate to that Risk. The Row Source of the
combo box would be a query based on tblControls.
 
J

JD McLeod

Can the primary key in tblRisks for RISKID and tblControls ControlID be an
autonumber? Is there a risk that the autonumber may reset and i end up with
duplicates? Or, should i come up with unique IDs for each risk and control,
maybe a combination of letters and numbers. thanks.
 
B

Beetle

You can use Autonumber for those PK fields. The Autonumber
data type will never repeat a value. The corresponding FK
fields in tblRiskControls must be Number (not Autonumber).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Table Design One to Many 2
Database design 1
Table Design or Report Design? 7
Db design review 14
Design of Risk Database 6
Database structure 1
Help with multiple reports please 2
Risk Cube 5

Top