Help on table structure

[darag2358]

I am doing a database that keeps track of mainframe security access. If someone goes to a certain screen on our mainframe, their ID must pass verification on some behind-the-scenes access "rules". I am documenting what each rule does, what mainframe system the rules are for, and what rules are tied to each other (for instance, if I want to update something on screen "X", I have to have the "X-update" rule as well as the rule or rules that allow(s) me to get to screen X in the first place).

I'm thinking that I would have one table that has:
Rule Name, Description, Owner, System

And one table with:
Rule Name, Rules tied to it

The 1st table would have the key field "Rule Name" as this would be a unique field. However the 2nd table does not have a unique field, because both fields could have duplicate values. This seems to me to be a problem, because I was taught that your tables should have at least one unique field. I could autonumber, but that doesn't really fix the structure. Am I missing something? Is this OK to do? Let me know if more info is needed. Thanks.

 

[KKilfoil]

You are trying to set up a table and a means of cross-referencing the same table.

In your 1st table, add a primary key autonumber field (call it RuleID) with a unique ID number. You can have your rule name be unique as well, but some things in Access work more smoothly if you link using integers rather than text values.

Your second table could be something like:

[tblLinkedRules]
LinkID - Key autonumber - serial number of linking record
ThisFK - integer, foreign key (FK) to the RuleID above
ThatFK - integer, another FK to RuleID above
RuleDate - date when rule implemented
etc

Set up a unique multi-field index on ThisFK and ThatFK to prevent duplication.

So the data might look like:

[tblRules]
RuleID Description etc
1 access to Screen X etc
2 X-Update etc
3 access to screen Y etc
4 Access to Network etc

[tblLinkedRules]
LinkID ThisFK ThatFK etc
1 2 1
2 3 1
3 2 4
4 3 4

So the first record in tblLinkedRules indicates that Rule 2 (RuleID=2) is related to Rule 1.

The fourth record indicates that Rule 3 is related to rule 4, etc


Does this make sense?

 

[darag2358]

Yes, that does make sense. I will try to implement that logic. Thanks for the help and taking the time to respond.