Waypoints: Entity Relationship (ER) Diagrams
Our initial entity relationship (ER) diagram includes the tables which we felt were most important to our design. These 13 tables and the logic behind their creation are listed in the paragraphs below.
The Artifact Table: Database design is centered on the concept that individual artifacts combine to create a historical archive. The artifact is the building block of the larger collection and as such became the central table in our design. Much like books on a bookshelf, artifacts have numerous descriptive properties such as creator, title, and a short description. Therefore, the artifact table contains these and many additional attributes which collectively describe a unique article.
The Creator Table: Realizing that an individual might be the creator for many artifacts in the collection we decided to implement a "creator" table which could be associated with many artifacts in the collection. This addition negated the need to have a person's name in the "artifact" table numerous times and allowed us the opportunity to generate queries to identify the artifacts in the collection that a particular person created.
The Donor Table: Much like a creator, a donor or group of donors can be responsible for contributing materials to the collection. As such, we built a "donor" table which has a one-to-many relationship with the artifact table. The attributes in this table were created in order to duplicate the information found in the "Deed of Gift" form used by the historian's office.
Historian and Historian Review: Not knowing how the system might be used in the future, we decided to include tables which allow system administrators to document who last reviewed a record in the database. The "historian" table is used solely to identify the individuals which review content for accuracy. And, the "historianreview" table associates a historian with one or many artifacts which they have reviewed along with a review date, comments, etc.
The Collection Table: Much like creators and donors which have a one-to-many relationship with artifact, a "collection" table was created in order to define the many-to-one relationship where a group of artifacts constitute a unique collection. In cases where a group of artifacts is donated to the archive (i.e. a scrapbook, personal photograph collection), it is valuable (and interesting) to be able to identify these materials as members of their own group.
The User Table: The "user" table was created so that administrative user interfaces could be restricted to those users allowed by the database.
Artwork, Historic Document, Oral history, Photograph, Video, and Physical Artifact: These six tables contain unique attributes not common to all artifacts. Originally, we discussed focusing on one type of artifact (e.g. photographs) or combining the different properties found in these tables with the artifact table (while allowing them to contain null values if they didn't apply). However, as we worked through our initial design - we wanted to maintain the ability to document all artifact types while keeping the design appropriately normalized. As such, we decided to create these six sub-class tables - leaving the unique attributes in each table.
With our basic framework in place, we had a system that allowed us to inventory our data. However, the structure did not support the faceted browsing interface which we hoped to implement. In our second ER diagram, we included the tables which would allow us to implement out browsing interface.
With a many-to-many relationship between artifacts and facets, we decided to create two one-to-many relationships in order to achieve the same end result. The resulting "association" tables serve as the bridge between the artifact table and each facet - each artifact having many associations and many associations referencing individual facets. Though the only artifacts made available to our development team were photographs, the browsing hierarchy has been designed to accommodate any type of descriptive metadata. However, the vocabulary in the faceted ontology will undoubtedly evolve as new artifacts are added to the collection.
The third version of our ER diagram reflects our current database design. The overall design remained the same; however, the user table was dropped because functionality for usernames/passwords was not yet implemented. Other changes include:
