Site records in databases: survey data storage

Survey records live in databases the same way they live in the ground: as layers that must be read in order. A field form becomes a row, a row becomes a table, and the table becomes a record that can be queried, corrected and cited years later. The work is less about software than about keeping the chain from trowel to archive intact.

Most project databases in the Southwest follow a simple shape. A site table holds one row per recorded location, keyed by a site number such as AZ I:3:45 or LA 12345. A component table holds occupations, phases or ceramic periods attached to that site. An artifact or feature table holds the observations themselves, each tied back to a provenience. The links between these tables are the record. Break a link and the sherd count floats free of the room it came from.

That structure is not unique to archaeology. Enterprise systems built on Oracle since the 1990s face the same problem of keeping related rows consistent, and a technical blog such as Oracle database practice documents the mechanics of constraints, sequences and bulk operations that keep those relations honest. The vocabulary differs, the discipline does not.

What does a survey record actually contain?

A single site record usually carries a site number, a temporary field designation, coordinates in a stated datum, elevation, land ownership, a site type, a condition assessment, and the initials of the recorder with the date. Attached to it are the observations: surface artifacts by material class, features such as hearths or rock rings, architecture if present, and photographs with roll and frame numbers.

The record is only as good as its provenance. A coordinate without a datum is not a location. A count without a collection unit is not a count. Field crews learn this early, and database managers learn it again when they try to join two seasons of work recorded under different conventions.

Paper forms still matter. Many projects record on paper in the field and enter data later, which means the database is a transcription and the paper is the primary document. Other projects enter directly on tablets, which removes one transcription step but adds the risk of silent edits. Either way, the database should carry a field for the original form number so the two can be reconciled.

How is survey data stored and queried?

Storage begins with controlled vocabularies. Site types, artifact classes, ceramic wares and condition terms are defined once and reused, so that a query for "habitation" does not miss records entered as "habitation site" or "residential". Lookup tables are unglamorous and they prevent most of the errors that surface later.

Queries fall into a few recurring shapes. Spatial queries ask what falls inside a project boundary or within a buffer of a proposed route. Temporal queries ask what dates to a period or a ceramic type. Attribute queries ask for all sites with masonry architecture above a certain elevation, or all records last updated before a given year. Most cultural resource management work is some combination of the three.

A well-built database answers these questions without exporting everything to a spreadsheet. That means indexes on site number, coordinates and period, and it means storing coordinates in a numeric field rather than as text. It also means deciding early whether the database is the system of record or a working copy, because that decision governs how corrections are made.

Corrections are where databases earn their keep. A site relocated in a later survey should update the coordinate and preserve the earlier value with a note. A misidentified sherd should be corrected in place with a revision date, not deleted. Deletion destroys the evidence that a mistake was made, which is itself part of the site's history.

How are records kept honest?

Honesty in a survey database is mostly a matter of constraints and audit trails. A constraint that requires every artifact row to reference an existing site prevents orphan records. A constraint that requires a recorder name and date prevents anonymous edits. A sequence or identity column gives every row a stable internal key that does not change when a site number is revised.

Audit trails matter more than most crews expect. A simple revision table recording who changed what and when turns a disputed count into a resolvable question. Some projects keep a shadow table of prior values. Others rely on periodic exports, which is weaker but better than nothing.

Bulk operations need the same care. Merging duplicate site records, updating a batch of coordinates after a datum conversion, or loading a season of new observations are all operations that can quietly corrupt a dataset if the join condition is wrong. Testing on a copy first is standard practice, and it is the step most often skipped under deadline pressure.

Backups are part of honesty too. A database that exists in one place on one laptop is not a record, it is a draft. Routine exports to a stable format, ideally with a documented schema, are what allow a project to be re-examined after the original staff have moved on.

What should be documented alongside the data?

A data dictionary is not optional. It lists every table, every field, every code value and every unit. Without it, a column named COND with values 1 through 5 is a mystery to anyone who did not build it. With it, the column is usable twenty years later.

Documentation should also record the survey methods that produced the data. Was the survey full coverage or probabilistic? What was the interval between transects? Were artifacts collected or recorded in place? These are not database fields, but they determine what the database can legitimately be used to answer.

Finally, the documentation should state the datum, the projection and the coordinate precision. A site plotted to the nearest second and a site plotted to the nearest minute are not equivalent, and a map that treats them as equivalent overstates its own accuracy.

Why does this matter for a site visit?

Readers who plan to visit a recorded site often start with a database query. Knowing how the record was made tells you what it can and cannot tell you. A record with precise coordinates and a recent condition assessment is a different kind of guide than a nineteenth-century note with a township and range.

It also tells you what to expect on the ground. Records that note active erosion, vegetation cover or recent vandalism are worth reading before a visit. Records that note only the presence of sherds may tell you nothing about access, terrain or land status, which are usually separate questions answered by the managing agency.

And it tells you how to report what you see. If a site has moved, eroded or been damaged since the last record, the database is where that observation belongs. A note with a date, a location and a description is a contribution to the record, not a complaint about it.

Databases do not replace field notebooks. They index them, and they make the index queryable. The survey record is only as honest as the constraints placed on it and the people who maintain it, which is true of any system that has to outlast the season that created it.

Source: nps.gov.