Table Design Question

[Guest]

I have a DB I use for training records at work. The training records the
employees are trained on are called Standard Operating Procedures (SOPs).
The SOPs are placed in categories. There are 4 departments that perform SOP
training. The table I enter the SOPs into is called tblSOPs. It consists of
the following fields:

Field 1- SOPID (a unique number for each SOP - the number cannot be
duplicated)
Field 2- SOPTitle
Field 3- Category (Can be one or up to four categories per SOP - For
example: Department A may need to be trained on SOP 123. Department A, B, &
C may need to be trained on SOP 234. etc.)

I also have a table for categories. (tblCategory)
Dept. A has 7 categories
Dept. B has 2 categories
Dept. C has 7 categories
Dept. D has 8 categories
This table consists of the following fields:
Category
DeptID

Someone told me, if a SOP (for example) has 4 categories assigned to it,
(using my current table structure), enter it 4 times (4 different records)
(with different values in the SOPID field), with the same SOPTitle and
different categories in each
record. I cannot do that because the SOPID cannot be duplicated. I'm
confused as to what to do now. MY SOPID has to remain unique. How shoud I
change my table structure so I can enter up to 4 categories per SOP? Do you
need to see all of my tables in the DB and how they relate to one another to
resolve this issue? I really need help with this. If someone has any
suggestions, I would really appreciate some help.

Thank you, Karen

 

[Guest]

You will need a new table that has SOPID and Category. Use the two fields to
create a compound key.

 

[Guest]

I do not see how. I am assuming your Category is something like Safety,
Finance, Security, EEO, etc.

 

[Guest]

Can't I just modify the tblCategory? Which consists of Category and
DepartmentID field?
Thank you

 

[Guest]

Why, oh why, can't I edit my posts when they go bad????

Anyway, what I tried to put in before . . .

tblSOP
SOPID
SOPTitle

tblDepartment
DepartmentID
DepartmentName

tblCategory
CategoryID
SOPID
DepartmentID

The tblCategory is where you relate the SOPs to the Departments. Once you
have linked the SOPID's and the DepartmentID's, you can populate the table
with all the ways that those two are related. You will list all of your SOPs
in tblSOP, and all of your Departments in tblDepartment. Each employee is
linked to a Department, which is linked to various Categories, which is now
also linked to pertinent SOPs. You no longer need tblDepartmentCategoryJoin.
Your tblTrainingEvents will look like this:

tblTrainingEvents
TrainingID
EmployeeID
CategoryID
TrainingDate

The CategoryID is what links back to the SOPID.

 

[Guest]

Below are my tables:

tblEmployees
EmployeeID (one to many with EmployeeID field in tblTrainingEvents)
FirstName
LastName
StartDate
EndDate
DepartmentID

tblDepartment
DeptID (one to many with DepartmentID field in tblEmployees) and (one to
many with DeptID field in tblDepartmentCategoryJoin)
DepartmentName

tblSOPs
SOPID (PROBLEM TABLE)
SOPTitle
Category

tblCategory
Category (one to many with Category field in tblSOPs) and (one to many with
CategoryID field in tblDepartmentCategoryJoin
DepartmentID

tblDepartmentCategoryJoin
JoinID
DeptID
CategoryID

tblTrainingEvents
TrainingID
EmployeeID
SOPID
TrainingDate


I don't know how to structure the tblSOPs table. SOPs can have one or up to
4 categories and the SOPID has to remain unique.
HELP!

 

[Guest]

Below are my tables:

tblEmployees
EmployeeID (one to many with EmployeeID field in tblTrainingEvents)
FirstName
LastName
StartDate
EndDate
DepartmentID

tblDepartment
DeptID (one to many with DepartmentID field in tblEmployees) and (one to
many with DeptID field in tblDepartmentCategoryJoin)
DepartmentName

tblSOPs
SOPID (PROBLEM TABLE)
SOPTitle
Category

tblCategory
Category (one to many with Category field in tblSOPs) and (one to many with
CategoryID field in tblDepartmentCategoryJoin
DepartmentID

tblDepartmentCategoryJoin
JoinID
DeptID
CategoryID

tblTrainingEvents
TrainingID
EmployeeID
SOPID
TrainingDate


I don't know how to structure the tblSOPs table. SOPs can have one or up to
4 categories and the SOPID has to remain unique.
HELP!