Relationships: Lookup table question

[gunslingor]

I have a table called "compensating measures" that has a primary key consisting of 3 fields: Patch ID, OS, vulnerability ID. All three fields comprise the key to guarantee logical duplication of data doesn't occur.

I also have a table called "cyber assets", which has one field called OS and a bunch of other device specific fields like location, room, model number, etc. Both of these OS fields have historically been enter-able without restriction, just a textbox essentially.

Now however, I want to make 100% certain that an OS in the "compensating measures" table cannot be entered unless it exists in the "cyber assets" table.

I tried to make the relationship between OS fields and it was indeterminate, which I think is bad but was not unexpected because neither OS field is a primary key.

So, I think I know what I need to do to keep things normalized and correct. I need to create a lookup table with only one primary key="OS"; then I link both tables to that OS table and both the "compensating measures" and "cyber assets" tables would be 1 to many. I think this is the correct way to deal with this, however, I'm concern about having to update this new lookup table.

If I want to add a new OS to either of the main tables, I would first have to add it to the lookup table correct? Is there a way around this?

 

[jdraw]

Your post may be more effective if you described to readers the business your proposed database is intended to support. Telling us about tables and keys is really telling us about HOW you have done something, but we really should hear the WHAT you are trying to accomplish and see if there are options. I'm sure a description of a day in the life of our business as it relates to the database in simple English would help.

 

[gunslingor]

Your post may be more effective if you described to readers the business your proposed database is intended to support. Telling us about tables and keys is really telling us about HOW you have done something, but we really should hear the WHAT you are trying to accomplish and see if there are options. I'm sure a description of a day in the life of our business as it relates to the database in simple English would help.

I was trying to keep it very simple, this is a basic question of maintaining database normalization without imparting functionality limitations or slowing down the process of filling in forms.

It would take a lot to describe everything, so all I can really tell you is that this is related to managing Vendor software patches for 100+ Vendors for 1500+ devices all falling under CIP Compliance. To achieve this intent of a fully functional patch management system for a given site, the database (patch management portion alone) consists of 15 relational tables, 10 forms, and about 8 reports... and a lot of VB code. What specific aspect do you have questions about? To clarify, the "compensating measures" table lists compensating measures related to the 3 field primary key, one of the fields of this primary key is the OS field. Someone should not be allowed to enter compensating measures for an OS not associated with a device in the "Cyber assets" table. Therefore, I need a relationship between the OS fields of the cyber assets and the compensating measures table... however this comes out as indeterminate, hence the questions about the lookup table.

If I implement the lookup table, here is how I want it to operate: if a user enters a new OS associated with a device via the cyber assets table, I want it to be automatically added to the aforementioned lookup table... I don't want them to have to open another add value popup to add the value to the combo box they are trying to set. However, if they add an OS to the compensating measures table, it must be one that is already listed in the lookup table, and thus the cyber assets table.

See where I am going with this? As far as the cyber assets table is concerned, the user should be able to add or change OSs willy nilly. But if they are compensating a vulnerability associated with a particular patch and OS, they should not be able to develop compensating measures for a vulnerable OS we don't even have on our networks.

 

[essaytee]

I was trying to keep it very simple, this is a basic question of maintaining database normalization without imparting functionality limitations or slowing down the process of filling in forms.

It would take a lot to describe everything, so all I can really tell you is that this is related to managing Vendor software patches for 100+ Vendors for 1500+ devices all falling under CIP Compliance. To achieve this intent of a fully functional patch management system for a given site, the database (patch management portion alone) consists of 15 relational tables, 10 forms, and about 8 reports... and a lot of VB code. What specific aspect do you have questions about? To clarify, the "compensating measures" table lists compensating measures related to the 3 field primary key, one of the fields of this primary key is the OS field. Someone should not be allowed to enter compensating measures for an OS not associated with a device in the "Cyber assets" table. Therefore, I need a relationship between the OS fields of the cyber assets and the compensating measures table... however this comes out as indeterminate, hence the questions about the lookup table.

If I implement the lookup table, here is how I want it to operate: if a user enters a new OS associated with a device via the cyber assets table, I want it to be automatically added to the aforementioned lookup table... I don't want them to have to open another add value popup to add the value to the combo box they are trying to set. However, if they add an OS to the compensating measures table, it must be one that is already listed in the lookup table, and thus the cyber assets table.

See where I am going with this? As far as the cyber assets table is concerned, the user should be able to add or change OSs willy nilly. But if they are compensating a vulnerability associated with a particular patch and OS, they should not be able to develop compensating measures for a vulnerable OS we don't even have on our networks.

I created a relationship between OS in Cyber to OS in Compensate (which is part of a three field Primary Key), enforced Referential Integrity and it worked. I entered test data and attempted to create a record in Compensate where there was no corresponding OS record in Cyber and I got the error message that there must be a record in Cyber, I could not save the record.

I deleted the relationship, entered test data in Compensate where no associated OS data in Cyber, then went about creating the relationship, enforcing referential integrity. I couldn't, was told to check data first. The error message returned did not use the term "indeterminate".

So, first, maybe check all your data that it does in fact exist in Cyber, if not, fix it, then apply the relationship.

As regards accounting for lookup values, that's just a matter of requerying your combo box recordsource after a user enters a new OS in Cyber.

Steve.

 

[CJ_London]

that has a primary key consisting of 3 fields: Patch ID, OS, vulnerability ID. All three fields comprise the key to guarantee logical duplication of data doesn't occur.

You don't need to make this the primary key to avoid duplications - you can simply do this by clicking on indexes in the ribbon when in table design view and select the fields you want in a combined index and the set the unique property to yes. As you can see - this is not the primary key