Waypoints: Data Dictionary
Throughout the development process, our data dictionary has evolved along with the database table structure.
Though remaining relatively consistent, several attribute data types were modified and data fields lengthened.
Changes include:
full text searching and is long enough to accommodate the long descriptions found in our data set.
on our decision to use URL references in our database instead of attempting to store images in the database.
artifact.
using scripts, we found that some of the fields were truncated and needed to be lengthened.
and LongDescription were indexed.
Our data dictionary is provided in two formats. The first (below) shows a log printout using MySQL to
describe each of the tables in the database. The second, available as an appendix, provides the
same information in in Microsoft Excel format and includes detail regarding foreign keys, indices.
mysql> show tables;
+-------------------------+
| Tables_in_waypoint |
+-------------------------+
| artifact |
| artwork |
| collection |
| creator |
| donor |
| event |
| eventassociation |
| historian |
| historianreview |
| historicdocument |
| location |
| locationassociation |
| mission |
| missionassociation |
| oralhistory |
| organization |
| organizationassociation |
| person |
| personassociation |
| photograph |
| physicalartifact |
| thing |
| thingassociation |
| unit |
| unitassociation |
| video |
| year |
| yearassociation |
+-------------------------+
28 rows in set (0.00 sec)
mysql> describe artifact;
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+----------------+
| ArtifactID | int(11) | | PRI | NULL | auto_increment |
| ArtifactName | varchar(100) | YES | MUL | NULL | |
| ArtifactType | enum* | YES | | NULL | |
| HistorianReviewID | int(11) | YES | | NULL | |
| ShortDescription | varchar(100) | YES | MUL | NULL | |
| LongDescription | text | YES | MUL | NULL | |
| SubmissionDate | date | YES | | NULL | |
| CreationDate | date | YES | | NULL | |
| Image | varchar(100) | YES | | NULL | |
| StorageLocation | varchar(100) | YES | | NULL | |
| Condition | varchar(100) | YES | | NULL | |
| Thumbnail | varchar(100) | YES | | NULL | |
| CreatorID | int(11) | YES | | NULL | |
| DonorID | int(11) | YES | | NULL | |
| CollectionID | int(11) | YES | | NULL | |
| ForRelease | tinyint(1) | YES | | NULL | |
+-------------------+--------------+------+-----+---------+----------------+
* Shortened to make printout readable. Reads:
"enum('Artwork','Historic Document','Oral History','Photograph','Physical Artifact','Video')"
16 rows in set (0.00 sec)
mysql> describe artwork;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| ArtworkID | int(11) | | PRI | NULL | auto_increment |
| ArtifactID | int(11) | YES | | NULL | |
| Medium | varchar(100) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> describe collection;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| CollectionID | int(11) | | PRI | NULL | auto_increment |
| ArtifactID | int(11) | YES | | NULL | |
| DonorID | int(11) | YES | | NULL | |
| CollectionName | varchar(100) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> describe creator;
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+----------------+
| CreatorID | int(11) | | PRI | NULL | auto_increment |
| ArtifactID | int(11) | YES | | NULL | |
| FirstName | varchar(50) | YES | | NULL | |
| LastName | varchar(50) | YES | | NULL | |
| MiddleInitialName | varchar(10) | YES | | NULL | |
| SuffixName | varchar(10) | YES | | NULL | |
| Affiliation | varchar(100) | YES | | NULL | |
+-------------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
mysql> describe donor;
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+----------------+
| DonorID | int(11) | | PRI | NULL | auto_increment |
| DonorGroupName | varchar(100) | YES | | NULL | |
| ArtifactID | int(11) | YES | | NULL | |
| FirstName | varchar(50) | YES | | NULL | |
| LastName | varchar(50) | YES | | NULL | |
| MiddleInitialName | varchar(10) | YES | | NULL | |
| SuffixName | varchar(10) | YES | | NULL | |
| Title | varchar(50) | YES | | NULL | |
| Affiliation | varchar(100) | YES | | NULL | |
| AddressStreet | varchar(100) | YES | | NULL | |
| AddressCity | varchar(50) | YES | | NULL | |
| AddressState | varchar(50) | YES | | NULL | |
| AddressZip | varchar(10) | YES | | NULL | |
| PhoneNumber | varchar(20) | YES | | NULL | |
+-------------------+--------------+------+-----+---------+----------------+
14 rows in set (0.00 sec)
mysql> describe event;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| FacetID | int(11) | | PRI | NULL | auto_increment |
| FacetName | varchar(100) | YES | | NULL | |
| ParentID | int(11) | | | 0 | |
| TopLevelFlag | tinyint(1) | YES | | NULL | |
| LeafFlag | tinyint(1) | YES | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> describe eventassociation;
+---------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+----------------+
| AssociationID | int(11) | | PRI | NULL | auto_increment |
| ArtifactID | int(11) | YES | | NULL | |
| FacetID | int(11) | YES | | NULL | |
+---------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> describe historian;
+-------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+----------------+
| HistorianID | int(11) | | PRI | NULL | auto_increment |
| FirstName | varchar(50) | YES | | NULL | |
| LastName | varchar(50) | YES | | NULL | |
| MiddleInitialName | varchar(10) | YES | | NULL | |
| SuffixName | varchar(10) | YES | | NULL | |
+-------------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> describe historianreview;
+---------------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------+------+-----+---------+----------------+
| HistorianReviewID | int(11) | | PRI | NULL | auto_increment |
| ArtifactID | int(11) | YES | | NULL | |
| HistorianID | int(11) | YES | | NULL | |
| ReviewDate | date | YES | | NULL | |
| HistorianReviewFlag | tinyint(1) | YES | | NULL | |
| Comments | text | YES | | NULL | |
+---------------------+------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> describe historicdocument;
+--------------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------+------+-----+---------+----------------+
| HistoricDocumentID | int(11) | | PRI | NULL | auto_increment |
| ArtifactID | int(11) | YES | | NULL | |
| NumberPages | int(11) | YES | | NULL | |
+--------------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> describe location;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| FacetID | int(11) | | PRI | NULL | auto_increment |
| FacetName | varchar(50) | YES | | NULL | |
| ParentID | int(11) | | | 0 | |
| TopLevelFlag | tinyint(1) | YES | | NULL | |
| LeafFlag | tinyint(1) | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> describe locationassociation;
+---------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+----------------+
| AssociationID | int(11) | | PRI | NULL | auto_increment |
| ArtifactID | int(11) | YES | | NULL | |
| FacetID | int(11) | YES | | NULL | |
+---------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> describe mission;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| FacetID | int(11) | | PRI | NULL | auto_increment |
| FacetName | varchar(50) | YES | | NULL | |
| ParentID | int(11) | | | 0 | |
| TopLevelFlag | tinyint(1) | YES | | NULL | |
| LeafFlag | tinyint(1) | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> describe missionassociation;
+---------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+----------------+
| AssociationID | int(11) | | PRI | NULL | auto_increment |
| ArtifactID | int(11) | YES | | NULL | |
| FacetID | int(11) | YES | | NULL | |
+---------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> describe oralhistory;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| OralHistoryID | int(11) | | PRI | NULL | auto_increment |
| ArtifactID | int(11) | YES | | NULL | |
| OriginalFormat | varchar(50) | YES | | NULL | |
| ReadBy | varchar(100) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> describe organization;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| FacetID | int(11) | | PRI | NULL | auto_increment |
| FacetName | varchar(50) | YES | | NULL | |
| ParentID | int(11) | | | 0 | |
| TopLevelFlag | tinyint(1) | YES | | NULL | |
| LeafFlag | tinyint(1) | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> describe organizationassociation;
+---------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+----------------+
| AssociationID | int(11) | | PRI | NULL | auto_increment |
| ArtifactID | int(11) | YES | | NULL | |
| FacetID | int(11) | YES | | NULL | |
+---------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> describe person;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| FacetID | int(11) | | PRI | NULL | auto_increment |
| FacetName | varchar(100) | YES | | NULL | |
| ParentID | int(11) | | | 0 | |
| TopLevelFlag | tinyint(1) | YES | | NULL | |
| LeafFlag | tinyint(1) | YES | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> describe personassociation;
+---------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+----------------+
| AssociationID | int(11) | | PRI | NULL | auto_increment |
| ArtifactID | int(11) | YES | | NULL | |
| FacetID | int(11) | YES | | NULL | |
+---------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> describe photograph;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| PhotographID | int(11) | | PRI | NULL | auto_increment |
| ArtifactID | int(11) | YES | | NULL | |
| OriginalFormat | varchar(50) | YES | | NULL | |
| LocationTaken | varchar(100) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> describe physicalartifact;
+--------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+----------------+
| PhysicalArtifactID | int(11) | | PRI | NULL | auto_increment |
| ArtifactID | int(11) | YES | | NULL | |
| Medium | varchar(100) | YES | | NULL | |
+--------------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> describe thing;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| FacetID | int(11) | | PRI | NULL | auto_increment |
| FacetName | varchar(50) | YES | | NULL | |
| ParentID | int(11) | | | 0 | |
| TopLevelFlag | tinyint(1) | YES | | NULL | |
| LeafFlag | tinyint(1) | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> describe thingassociation;
+---------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+----------------+
| AssociationID | int(11) | | PRI | NULL | auto_increment |
| ArtifactID | int(11) | YES | | NULL | |
| FacetID | int(11) | YES | | NULL | |
+---------------+---------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> describe unit;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| FacetID | int(11) | | PRI | NULL | auto_increment |
| FacetName | varchar(50) | YES | | NULL | |
| ParentID | int(11) | | | 0 | |
| TopLevelFlag | tinyint(1) | YES | | NULL | |
| LeafFlag | tinyint(1) | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> describe unitassociation;
+---------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+----------------+
| AssociationID | int(11) | | PRI | NULL | auto_increment |
| ArtifactID | int(11) | YES | | NULL | |
| FacetID | int(11) | YES | | NULL | |
+---------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> describe video;
+--------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+----------------+
| VideoID | int(11) | | PRI | NULL | auto_increment |
| ArtifactID | int(11) | YES | | NULL | |
| Length | varchar(50) | YES | | NULL | |
| VideoLocationTaken | varchar(100) | YES | | NULL | |
| OriginalFormat | varchar(100) | YES | | NULL | |
+--------------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> describe year;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| FacetID | int(11) | | PRI | NULL | auto_increment |
| FacetName | varchar(100) | YES | | NULL | |
| ParentID | int(11) | | | 0 | |
| TopLevelFlag | tinyint(1) | YES | | NULL | |
| LeafFlag | tinyint(1) | YES | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> describe yearassociation;
+---------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+----------------+
| AssociationID | int(11) | | PRI | NULL | auto_increment |
| ArtifactID | int(11) | YES | | NULL | |
| FacetID | int(11) | YES | | NULL | |
+---------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)