History Database Project: Creating an “Entity Relationship Diagram”

To build the History Research Database in FileMaker, we need to define our “entities”–that is, the core sorts of data that will make up the database. Entities are also known as “tables.”  We might think of an entity as a single spreadsheet: each row in the spreadsheet is a separate record in the database; each column is a field, or a general type of data. The power of a relational database is that we can put these different entities together to build searches, generate reports, and organize activities.  Here’s my first crack at an Entity Relationship Diagram (ERD) with three possible fields in each table (there will be many more in fact).

Screen Shot 2017-12-11 at 10.13.19 PM


I propose four entities in the database:

Sources: information about books, articles, archival materials, etc.

Objects: research object we want to store and keep track of, such as: documents, maps, images, graphs, PDFs, statistical tables, website URLs, etc.

Notes: our annotations, comments, and transcriptions of sources and objects, tagged with subject headings and linked to writing and digital projects.

Projects: what we produce from our analysis of our sources, objects, and notes, such as chapters, books, articles, visualizations, annotated bibliographies, etc.

As this ERD shows, each entity has a defined “one-to-many” relationship with at least one other entity.  Getting this right is the key to making a relational database work.  The connectors indicate that every Source in the database can have many Objects (for example, I recently took a bunch of photos of maps in the wonderful Historical Atlas of Maine: one book, many maps), but not the other way around.  I can take many Notes from this book, my Source, but I’m going to limit myself to no more than one Source per Note.  As I build my Projects, I will probably associate many Notes and Objects with a particular Project, but not the other way around.  (This last set of relationships is a bit tricky, and I will possibly have to revise).

So, for now, this is my presumed ERD for the History Research Database.  Comments?  Suggestions?  I’ll keep working on this and post a full diagram with all of the fields as I make more progress.