Designing the Database

The SFCA website is built on a database containing all the Flood Claims, which were originally transcribed by Victorian clerks into 11 large volumes.

However, the hand-written registers were not designed for the rigid constraints of a database. For example, margins were freely used for supplementary information; ditto marks (" " ") were used to save writing; and the clerks would even indicate areas measured in acres, roods & perches with a, r, p written above the three numbers – see this illustration taken from claim 5731.

Since individual entries can be taken out of the context of the preceding and following particulars, ditto marks clearly had to be expanded. Separate database fields were created for marginal notes.

More seriously, some of the more complex claims were entered in tabular formats on the page. I had already decided that in the database, one ‘particular’ would be that text associated with a given value in the ‘amount claimed’ column. Where tabular information was associated with a single particular, such as claims 6177, 6395, the description in the database includes HTML markup. When viewed in the database, this shows the HTML tags, but when displayed in a web page, it becomes properly formatted. For a few more complex cases where the tabular information spanned several ‘particulars’, such as claim 2662, a separate database structure was created which enabled building tabular presentations spanning a number of ‘particulars’.

Any database has to be designed bearing in mind both the structure of information being represented, and the ways in which that information will be used. In this case that meant looking at the information held in the registers, and how it was structured; the differences (and similarities) between claims for damage, for injury, and for loss of life, and the degree of variation between individual claims. The uses it would be put to included faithfully (as far as possible) representing the claims as they were in the original registers, searching the claims, and performing financial and other analyses.

Monetary values are stored in the database with pounds, shillings and pence in separate fields, with special functions for doing arithmetic in ‘old money’.

Though the original project plan was to create the database, then do the data entry, then develop the website, we decided to invest time up-front in developing the web pages which would display the claims. This was the most efficient way of providing facilities for data-entry validation, as well as providing on-going confirmation of the database design and web page design.

The web page template for representing the claims – developed using Macromedia ColdFusion – is a complex arrangement, with cells spanning rows and columns depending on the details of different claims – the underlying grid contains 7 columns.

One of the difficulties of this project has been that it becomes too interesting, and it is difficult to avoid distractions. Looking into units of area mentioned above, I discovered that an acre was originally the area which could be ploughed by a team of oxen in a day¹ (or rather in a morning, with the afternoon for re-fuelling the animals). Originally a unit of taxation more than size, it eventually became standardised at 220 yards by 22 yards – 220 yards being a furlong, or furrow-long. 22 yards was a chain, or 4 poles – or the length of a cricket pitch.^2,3 Sorry, getting distracted again...

Chris Veness, Movable Type