OT : Advice on SQL database design

[Robert Brenstein]

On 01.07.2014 at 1:23 Uhr +0100 Alex Tweedly apparently wrote:
>I guess there's a meta-question about "what's a good book/source on 
>SQL database design ?"
>
>I am trying to design a database. There is one table which contains, 
>let's say, Armed Forces Personnel
>  - each row is a different person
>  - columns include the obvious .... name, date of birth, ... that 
>are common to all
>  - there is a column saying which armed force the person is in
>       - and then there are additional, DIFFERENT fields depending on 
>which one it is
>

You will get lots of advice, I am sure. Database design is a science 
and an art. What helps often is to identify data along the lines of

lists -- lists of values that are more or less fixed, like types of 
armed forces

data -- info that is varied from data record to record, like people names

links -- tables that cross-connect data and lists (but also may have 
own fields related to that connection, like a date of cross-linking)

Such division is not fixed but it helps to identify different types 
of information and initial structure.

Things to consider in the next step are variations (like people 
changing names), permutations (like people belonging to more than one 
armed force or changing their skills), expansions (like incorporation 
of addditional lists or further personal data), whether list data 
need to be frozen (here I mean that renaming an entry in a list will 
automatically rename it for all records; however, in some database, 
the entry for a given record should remain as it was at the time of 
entry even if the list is reworked later on), etc.

Next you consider various views and reports that you need to produce 
and see whether the structure supports them without too many 
complications.

Robert