From: Lionel Cons [lionel.cons@cern.ch]
Sent: 05 October 2007 16:03
To: David Myers
Cc: project-eu-isseg-pb-discussion (ISSeG Project Board discussion)
Subject: RE: ISSeG Web Site, Questionnaire and Databases

David Myers writes:
 > For the recommendations we concluded that it would still be beneficial  > to have these in a database, both to simplify their initial production,  > as well as to deal with any future additions and changes. As it  > happens, the design of the records in such a database has already been  > done by Lionel to 50% or more after our last face-to-face meeting, and  > Lionel has "volunteered" to complete this (i.e. include any missing  > "pink elephants": Recommendations, Threats, questions, etc.). The  > design could then be implemented in Access or Oracle.

All,

As agreed here is what I would propose. This is a kind of first draft, open for discussion. Your feedback is very welcome. Yes, it's long, but the devil is in the details...

Cheers,

Lionel

Here is a proposal for a database structure to hold the recommendations (and related objects), in order to support a database driven web site. This is written in generic terms and should be easy to translate into real database code. Each block is a table; each line is a field.

The goal is _not_ to be able to answer the questionnaire online. This would require many more fields as all the coefficients and matrices would need to be represented somehow.

questions: [as in CD4a]
 - id(integer): used internally
 - number(integer): exposed to the user
 - text(string): to hold the text of the question
 - notes(string): private text seen by the editor but not displayed on the web

threat_families: [as on our web site]
 - id(integer): used internally
 - title(string): e.g. Compromising, Human, Failure, Environment...
 - description(string): to fully describe

threats:
 - id(integer): used internally
 - number(integer): exposed to the user
 - title(string): to list or concisely describe, one line only, 15 words max
 - description(string): to fully describe
 - examples(string): to give more concrete examples [as in CD4a]
 - vulnerabilities(string): to show the associated vulnerabilities [as in CD4a]
 - top(integer): if greater than 0, is one of the ISSeG "top threats"
 - notes(string): private text seen by the editor but not displayed on the web

dimensions:
 - id(integer): used internally
 - title(string): i.e. Technical, Admin or Education
 - description(string): to fully describe

keywords:
 - id(integer): used internally
 - title(string): e.g. Linux, Windows, User, Account...
 - description(string): to fully describe

recommendations: 
 - id(integer): used internally
 - number(integer): exposed to the user
 - title(string): to list or concisely describe, one line only, 15 words max
 - description(string): to fully describe (i.e. what)
 - rationale(string): to justify/convince (i.e. why)
 - notes(string): private text seen by the editor but not displayed on the web

hows:
 - id(integer): used internally
 - title(string): to list or concisely describe, one line only, 15 words max
 - description(string): to fully describe
 - recommendation(id)

recommendations_dimensions: [to link n-to-n recommendations and dimensions]
 - recommendation(id)
 - dimension(id)

recommendations_keywords: [to link n-to-n recommendations and keywords]
 - recommendation(id)
 - keyword(id)

recommendations_threats: [to link n-to-n recommendations and threats]
 - threat(id)
 - recommendation(id)

recommendations_questions: [to link n-to-n recommendations and questions]
 - recommendation(id)
 - question(id)

threats_questions: [to link n-to-n threats and questions]
 - threat(id)
 - question(id)

threats_families: [to link n-to-n threats and families]
 - threat(id)
 - family(id)

notes:
 - we have both ids and numbers: the ids are unique and never change, they
   are also internal and never shown to the user; the numbers are visible
   and may change, for instance when an object is added or removed
   (e.g. adding a question in the middle)
 - the text/strings should have some formatting; in order to be standard,
   it's better to agree on a subset of HTML, for instance: <I>, <B>, <P>,
   <UL>, <OL>, <LI> and <A>
 - there are no formal "hints" or "links" fields, they should appear as
   normal text, for instance the hints could appear as a bulleted list
 - the separate tables are the only clean and portable solution to
   represent n-to-n links
 - it's probably overkill to represent dimensions like that, maybe a bit
   field or a string (made of [TAE] characters) would be enough
 - the assets are not represented here, should they be added?