library design

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Elise R - 29 Jul 2005 18:38 GMT

I need a library to hold Standard Operation Procedures. There is appx 1200
SOPs that we use. Whenever an SOP is updated, the reference number changes
(example: AB.01.0.1 CHANGES TO AB.01.0.2) on the implementation date and the
old one is archived (archival date may be different for each of the
11departments). What is the best way to set this up? By the way, some SOPs
belong to only one department and some may belong to seven (7) departments,
etc. How can I enter the information in once and have it go to the other
departments, if any?

Any help would be greatly appreciated.

Reply to this Message

KARL DEWEY - 29 Jul 2005 19:35 GMT

You should not have it but once. A single department needs to control the
document and coordinate changes with the others that use or are affected by
it.

> I need a library to hold Standard Operation Procedures. There is appx 1200
> SOPs that we use. Whenever an SOP is updated, the reference number changes
[quoted text clipped - 6 lines]
>
> Any help would be greatly appreciated.

Reply to this Message

Hermawih - 29 Jul 2005 19:56 GMT

Create 3 tables.

A. table for SOP :
SOPID : Number (PK)
SOPName : Text (ie : AB.01.02)
Status :Number (1 = active , 2 = Archives)
B. table for department :
    DeptID : Number(PK)
    DeptName:Text
C. Linking Table for SOP and Depatment .
    DeptID : Fkey
    SOPID : FKey
ArchivedDate:Date/time .. When it is archived

Here is the diagram ,

DEPT DEPTSOP SOP
---------------------------
DeptID DeptID SOPID
DeptName SOPID SOPName
....... ArchivedDate Status
...... ...... .....

if you want to changed the SOP then
A. Create new sop on table SOP with status = 1 . Changed old SOP with
status = 2
B. Stamped the archived date on linking table .

Is that what you want ???

Signature

Hermawih

Reply to this Message