Building domain-agnostic databases using design patterns

Authors: Ian Johnson

Abstract: The conventional approach to designing SQL databases is tightly coupled to the specifics of the data to be recorded; tables typically reflect the entities modelled, the relationships between these entities and lookups for controlled values; the interface software is built to manage those structures. Consequently, this approach to managing data tends to generate problem-specific databases, which are tightly tied to a particular method of recording. The structure itself carries much of the semantic payload, which may also be embedded in locally programmed functions. While there are plenty of tools for streamlining development, from database wizards to UML and frameworks, this approach to database design is ultimately a programming approach with limited portability across projects. At the other end of the spectrum, spreadsheets have a very low entry barrier and can be very efficient for handling structurally simple and repeatable datasets, such as specialist analyses involving samples, quantification and graphical display. However, when turned towards heterogenous collections which should be modelled as separate entities and relationships, they have encouraged unspeakable crimes against data modelling! Such spreadsheet 'databases' may end up a spaghetti soup of multiple entities per table, rampant redundancy, uncontrolled coding, multiple values per cell, positional significance, and cells blown out with discursive text. They are often a response to the mismatch between the data modelling required and the expertise and/or technical resources available. In the mid 1990s, Jens Andressen and Torsten Madsen at Aarhus University developed an elegant model for their IDEA database system, which reduced the archaeological excavation landscape to just three main tables - deposits, finds and constructs - which could then be adapted to a wide range of different recording systems. A decade later I started designing Heurist (HeuristNetwork.org) based on an even simpler database construct of just two main tables - records and data values - which are agnostic on the nature of the entities recorded. The records table simply defines the record type and provides a foreign key value identifying the set of key-value pairs in the data values table which form an entity (as with IDEA, another 40 tables manage the coding and content of these tables). A further decade later, the FAIMS project (FedArch.org) is using an internal database with the same two main tables - entities and attributes - for their field data collection tablet app and synchronisation server. In this paper I will show how widely disparate archaeological data can be modeled in such a meta-database hosted on top of a conventional relational database manager (Access for IDEA, MySQL for Heurist, SQLite for FAIMS). The key to these systems is not to deal directly with the structure of the data, but to identify commonly occurring database design patterns and to implement generic procedures to handle them independent of context. In the paper I will formally define a set of design patterns appropriate to archaeological data, and assess the Heurist and FAIMS applications against this set of patterns. By doing this we can identify opportunities for further development.