Database design

This page is as much for my benefit as yours.

The database has the following tables:

• Houses
• House advertisement details
• Persons
• Person-House relationships (usually reflecting residence in the house)
• Person-Person relationships

There is an underlying FileMaker Pro database from which I export data for the Web site:

• All houses ‘in scope’, sorted by House name
• All advertisement details for ‘in scope' houses, sorted by House name, date
• All persons, sorted by Person name (surname first)

So there are people in the list for whom you won't find any information. That doesn't mean I don't know anything about where they lived, they just didn't live in my ‘in scope’ houses. An out-of-scope house might be included later, especially if you give me more information about it!

• House-Person relationships for ‘in scope’ houses, sorted by House name
• Person-Person relationships for ‘in scope’ houses, sorted by the name of ‘Person 1’ (in most cases I have put both directions of the relationship in, but not always)

Generation of the Web site

Each House and each Person has Serial number, which become the ID s by which everything is linked together. The Serial numbers are regenerated before each generation (I may stop doing this, but at the moment, it ensures that when you page through using Next and Previous, you will see houses or people in alphabetical order.)

Each of the tables is exported to csv (comma-separated variable) format and a record delimiter (\r\n) is added to each record. The ‘real’ commas are temporarily edited to something else, so that Perl can use the comma delimiting the items of data as a ‘knife’. Then a Perl script separates each item of data from its record and writes an XML file. The commas are then changed back to ‘real’ commas, and the XML files are ready to be used by their respective HTML pages (which use Adobe Spry widgets in case you wanted to know).