Stuck on DB design

[lithium]

Okay, I have a large project I have been working on. I have four tables (can possibly be 3, or whatever is suggested). Essentially I am designing a form for someone to add new policies (these can just be Corporate policies, or InfoSec Policies). The thing I have always been terrible with is databases and need some guidance. The corporate policies will be the Parent (on a Treeview control) and each Corporate Policy can contain many InfoSec Policies and each InfoSec Policy (table) can contain many "Implementable Features" (Table), as well as many Implemented Controls (table).




My concern is what is the most efficient way to build the tables and link them together in respect to it being a one-to-many on each side?

tblCorp                                           tblInfoSec                                 tblFeatures                             tblControls
-------                                           ---------                                  ------------                          ------------
(PK)Policy_ID                                 (PK)InfoSec_ID                           (PK)Feature_ID                      (PK)Control_ID
Policy_Name                                   InfoSec_Name                             FeatureDesc                          ControlDesc
Owner                                                Owner                                      Owner                                     Owner
Scope                                                Scope                                     InfoSec_ID                              Feature_ID
Statement                                        Statement
                                                        Policy_ID

[lithium]

The hardest thing for me is that, There are going to be multiple Corporate Policies, with possibly many InfoSec Policies under each Corp Policy. As well as many impl. features and controls attached to each infosec policy,

[ssanfu]

You have an "Owner" field in all 4 tables. If you create a new Policy (record) in tblCorp, is tblCorp.Owner the same as tblInfoSec.Owner?



From what you have provided, this is how I would design the tables:

                                                                                                           tblControls
                                                                          tblFeatures                      ------------
                                      tblInfoSec                          ------------                     ControlID_PK
tblCorp                               ----------                          FeatureID_PK (Autonumber) ---->> FeatureID_FK
-------                               InfoSecID_PK (Autonumber) ------>>  InfoSecID_FK                     Control_Desc
PolicyID_PK (Autonumber)  --------->> PolicyID_FK                         Feature_Desc                     Control_Owner
Policy_Name                           InfoSec_Name                        Feature_Owner                                 
Policy_Owner                          InfoSec_Owner                                                                     
Policy_Scope                          InfoSec_Scope                                                                     
Policy_Statement                      InfoSec_Statement

As well as many impl. features and controls attached to each infosec policy,

So is it:
1 to many between "infosec policy" and "impl. features"
and
1 to many between "controls" and "impl. features" ???

OR

1 to many between "infosec policy" and "impl. features"
and
1 to many between "impl. features" and "controls" ???

[lithium]

Basically:

1 to many between "Corp Policy" and "InfoSec Policy"
1 to many between "InfoSec Policy" and "Implementable Features"
1 to many between "Implementable Features" and "Implemented Controls"



So you had it right with:

1 to many between "infosec policy" and "impl. features"
and
1 to many between "impl. features" and "controls" ???

and

As well as many impl. features and controls attached to each infosec policy,

There will be, lets just say 10 Corporate Policies. Each policy can have many different InfoSec Policies attached under it, and each IS Policy can have several Imp. Features, these features will have many controls established to accommodate the single feature (the feature is basically a statement saying what we could do to prevent things, and the control are actual mechanisms installed to achieve that goal for that feature".

[lithium]

As for "Owner" there truly is only two Owners, which are Info Protection and Info Security. Info Security will only be able to add information and edit things relating to the Imp. Controls. They can't add or edit Corp policies, IS Policies, or Features

[ssanfu]

Not quite sure I understand yet.

If there can only be one entry into tblControls.Control_Owner, do you really need this field?

In any case, it seems like the structure should work. Now it is just a matter of building the forms/sub-forms.

[lithium]

Thank you for all your help. Yeah the Owner field is still questionable to me as well. I mean, I do want the form to pull the data for who is the person responsible for editing/upkeep on that policy so I assume I need it in each area.