I'm looking for some guidance in database design. I will present my problem and my idea of how to solve it and I would welcome any comments.
I suspect that there may be a simpler way than what I am about to propose.
30+ years in computing but still a total novice with respect to data bases.
That's what comes from working in real-time and embedded systems.
I need a data entry method to support a hierarchical selection process.
What I need is something similar to the ASN.1 OID where every element is described by a path through a tree.
So, the first combo box gives a choice of possibilities at the top level.
Making a selection there will either terminate the process or generate another combo box for the choices available at level given the level 1 choice.
The system only requires 3 levels so it's not as bad as an OID.
It will help to see exactly what I'm talking about.
The following table is extracted from a United Nations Economic Commission for Europe (UNECE) working group on cyber security for road vehicles and describes a bunch of threats that must be guarded against.
High level threat classes are divided down into sub-level threats and in turn into specific examples of the vulnerability. The data entry process I imagine has three combo boxes where the first selects a High Level Threat and once selected a second combo box would be populated from the sub level threats connected to the selected high level threat. Finally a third combo box would select the leaf example. The result of all this is an ID string that specifies a path through the tree.
I think I can do this with three tables for the three major columns of the example table.
The first combo box would have all of the elements of the High Level Threat column, there are eight of these. That table would also contain pointers (indexes) into the next table indicating where to find the sub-level threats associated with the first choice. Each sub-level threat entry would have indices to select the associated leaf level vulnerabilities.
The second and third combo boxes would be populated by a query derived from the previous choice.
So does this sound like a sensible approach?
Any comments or suggestions would be most welcome.
Hank Cohen
This is just the first few lines of the table. I have attached a spreadsheet with the whole thing.WP29Annex5Table1A.zip
High Level Threat index sub-level threat index Example of vulnerability 1Threats regarding back-end servers related to vehicles in the field 1 Back-end servers used as a means to attack a vehicle or extract data 1 Abuse of privileges by staff (insider attack) 2
Unauthorized internet access to the server (enabled for example by backdoors, unpatched system software vulnerabilities, SQL attacks or other means) 3
Unauthorized physical access to the server (conducted by for example USB sticks or other media connecting to the server) 2
Services from back-end server being disrupted, affecting the operation of a vehicle 1 Attack on back-end server stops it functioning, for example it prevents it from interacting with vehicles and providing services they rely on 3 Vehicle related data held on back-end servers being lost or compromised ("data breach") 1 Abuse of privileges by staff (insider attack) 2 Loss of information in the cloud. Sensitive data may be lost due to attacks or accidents when data is stored by third-party cloud service providers 3 Unauthorized internet access to the server (enabled for example by backdoors, unpatched system software vulnerabilities, SQL attacks or other means) 4 Unauthorized physical access to the server (conducted for example by USB sticks or other media connecting to the server)