Creating a database that represents a historical primary source is both challenging and exhilarating. When I first learned of using Digital Tools to answer historical inquiries through textual analysis and data entry my brain reached a certain ‘Aha’ moment. Primary sources such as Muster Rolls, or the Census (large static data sets) were suddenly able to be rediscovered in their pure form. It was as if I was Neo in the Matrix, and I saw these volumes of archived information in binary code- a digital world in which the extraction of this unchanged data could be visualized, and brought to life in a three-dimensional world…
I swallowed the Digital History pill, and it is down, down the rabbit hole I willingly fell.
My goal was simple: Create a database that both represents the original source and provides a statistical figure of the role of Desertion among Connecticut Line soldiers in the American Revolution. With little to no experience with database creation, I set out with a pencil and spiral notebook to start recording each and every soldier that deserted, and all the pertinent data related to their enlistment. I soon traded the notebook for a laptop and Excel document once I had a better idea of the data I was extracting. Thumbing through the 600+ pages, the Deserters- approximately 1400 of them became the foundation of my database.
Using the Records of Service (Muster Rolls), I compiled a list in Excel of Deserters from 1775-1783. This included information such as: Soldier’s Name, Soldier’s hometown, Rank, Captain’s Name, Captain’s Hometown, Company Name/ Number, Company Year, Colonel’s Name, Colonel’s Hometown, Regiment Name/ Number as well as all Enlistment Term Information. Due to the large amount of data collected, I created a flat file in Excel before importing it into Access. In order to import the thousands of records, it was essential that I clean up my data. Once the data was normalized, I created an entity-relationship database in Access.
The main challenges with the import of data resided in the creation of relationships. Creating relationships that reflected the research as well as the research question proved to be the most difficult. In hindsight, it may have been more effective to create the relationships and the forms in Access and enter the data in as I collected it in order seamlessly establish connections. Creating a flat file database in Excel led me to believe my data had to work in the way it was created, not realizing that my original thoughts would not convey in Access. In other words, I was confident of how my database would look without understanding how a relational database acts.
As far as representing the historical event of Desertion, much care had to be given to the source data and its extraction. I decided to include duplicate names, knowing that the soldiers could indeed be the same deserter with different enlistments. Due to my inability to guarantee the individual deserter without any other identifying factors, I treated them as separate entities through the use of Primary Keys. Using a query function, I can easily access the duplicate names or possible misspelling of any given soldier. In addition, the categories or fields within each table were extremely difficult to keep to a minimum. For instance, is desertion an enlistment event, or should it have a table all of its own? Since most Deserters returned to the war effort, sometimes to desert again and return it was decided that the desertion event is a term of enlistment data.
While Access proved to be helpful in transforming my data into a relational model, the learning curve of executing a humanities based database in software that was designed for a business model, proved to have problems regarding transference. For example, dates in the Excel format do not export to Access in a Month/Day/Year format without error. Dates before the 20th Century are based on a decimal system (based on years starting with one) which skews the clarity for the viewer. It is possible to overcome this problem using Visual Basic Editor, but for the purpose of my research, I decided to avoid the extraneous work of creating a module for my data set. Instead, I separated the date into column delimited values- three separate fields/columns with three different numbers (representing Month|Day|Year). As a result of using this method, I discovered an important component of table building; the more simplistic, separate, and detail oriented the categories are in a database (normalized), the more flexible the query function is. Even more interesting, the better the clarification of data, the more poignant the questions you can ask and ideally answer.