Lawrence Township Historical Artifacts Catalog
From Lhi
The Lawrence TownshipHistorian, Dennis Waters has inherited a database from previous Historians. It appears to contain a catalog of historical artifacts. He is working with a Mercer County History Librarian, Laura Nawrocik. The material here is also of interest to the Lawrence Township Historical Society.
So... This page (and others on the wiki) can be used to collaborate in deciphering the database and creating a useful tool for the community.
Contents |
Conversion Process
A Perl script was written to import Foxbase-like files into a MySQL database. It ran like this:
bill@nex:~/shelf/hist> ./dbf2mysql Processing HDATE.DBF -- 14861 records Processing HITEM.DBF -- 3099 records Processing HPLACE.DBF -- 6890 records Processing HSUBJ.DBF -- 19445 records Processing HFAC.DBF -- 5 records Processing HNAME.DBF -- 22532 records Processing HSEE.DBF -- 169 records Processing HTYPE.DBF -- 21 records
So 8 tables were converted, and they were examined. A description of probable organization follows.
The Master Item List
There is a record for every cataloged item in the item table. The item record seems to contain all of the main descriptive catalog info. The LOC field can apparently be used to join with other tables that serve as alternate indices. LOC probably also serves as a location descriptor which is unique.
Alternate Indices
Items can be located using alternate references through these tables:
Date
The date table contains dates for use in a pattern-matching search. An example of usage can be inferred from viewing sample data from the table:
mysql> select * from date limit 3; +------------+----------+-------------+ | DATE | DTENTRY | LOC | +------------+----------+-------------+ | 01/01/1712 | 19910124 | S021:01-04 | | 00/00/1697 | 19910124 | S021:01-04 | | 00/00/1869 | 19910124 | S021:01-04 | +------------+----------+-------------+
Name
The name table provides an index by person name. Sample data:
mysql> select * from name limit 3; +----------+-------------+-----------------+ | DTENTRY | LOC | NAME | +----------+-------------+-----------------+ | 19910124 | S021:01-01 | VanCleve, Aaron | | 19910124 | S021:01-01 | Brearley, James | | 19910124 | S021:01-01 | Brearley, John | +----------+-------------+-----------------+
Place
The place table provides an index by place name. Sample data:
mysql> select * from place limit 3; +----------+-------------+-----------------+ | DTENTRY | LOC | PLACE | +----------+-------------+-----------------+ | 19910124 | S021:01-01 | Maidenhead | | 19910124 | L032:1A-L0 | Cold Soil Road | | 19910211 | M002:01-07 | Assunpink Creek | +----------+-------------+-----------------+
Subject
The subj table provides an index by subject. As of this wrinting, there are 445 distinct subjects in 19,445 records. Sample data:
mysql> select * from subj limit 20; +----------+-------------+---------------------+ | DTENTRY | LOC | SUBJECT | +----------+-------------+---------------------+ | 19910418 | C001:01-05 | Army | | 19910418 | C001:01-05 | Maps | | 19910418 | C001:01-05 | Army - British | | 19910418 | C001:01-05 | Battle of Trenton | | 19910418 | C001:01-05 | Battle of Princeton | | 19910305 | C001:01-10 | Maps | | 19910305 | C001:01-11 | Maps | | 19910312 | C001:01-14 | Maps | | 19910312 | C001:01-15 | Maps | | 19910312 | C001:01-15 | Planning | | 19910312 | C001:01-16 | Maps | | 19910312 | C001:01-18 | Maps | | 19910312 | C001:01-18 | Roads | | 19910312 | C001:01-21 | Maps | | 19910312 | C001:01-22 | Maps | | 19910418 | C001:01-26 | Maps | | 19910418 | C001:01-26 | Budgets | | 19910312 | C001:01-32 | Maps | | 19910312 | C001:01-33 | Maps | | 19910418 | C001:02-03 | Maps | +----------+-------------+---------------------+
Other Tables
fac Table
The fac table is a list of (5) records storage facilities:
mysql> select * from fac; +------+-----------------+ | CODE | DESCRIP | +------+-----------------+ | H | Hist. Cage | | L | Mer. Cnty. Lib. | | C | Clerk's Office | | M | Mun. Bldg. | | O | Other | +------+-----------------+
The code is used in the item table.
type Table
The type table is a medium type code (CD, photo, etc.):
mysql> select * from type; +------+---------------------------+ | CODE | DESCRIP | +------+---------------------------+ | A | Archives | | F | Artifacts | | B | Books | | M | Manuscripts | | C | Maps | | CD | Map Drawer | | I | Miscellaneous | | N | Photographic Negatives | | P | Photographs | | E | Posters | | R | Proclamations | | U | Publications | | L | Slides | | S | Subject File | | TA | Audio Tapes | | TV | Video Tapes | | H | Other | | AV | Archives in Vault | | D | Microfilms | | AH | Archives in Health Office | | W | Artwork | +------+---------------------------+ 21 rows in set (0.00 sec)
see Table
The see table is a cross-reference associating phrases which are synonymous in the Lawrence historical context.
Sample data:
mysql> select * from see limit 5; +----------------------------+-------------------------------------+ | ALIAS | DESCRIP | +----------------------------+-------------------------------------+ | Lawrence Township | Lawrenceville | | Township Seal | Seal | | Lawrence Road Fire Company | Lawrence Volunteer Fire Association | | Army - British | British Army | | Lawrence Township | Maidenhead Township | +----------------------------+-------------------------------------+
Location Key
The LOC field is the accession number given to the folder when it was entered into the index. It is the main index key that points to the other index fields in the software and also serves as the shelf number of the folder in the physical archive. In the examples below, LOC is a specific folder and the reference in the PLACE column shows that information about that place can be found in the particular folder.
mysql> select * from place limit 3; +----------+-------------+-----------------+ | DTENTRY | LOC | PLACE | +----------+-------------+-----------------+ | 19910124 | S021:01-01 | Maidenhead | | 19910124 | L032:1A-L0 | Cold Soil Road | | 19910211 | M002:01-07 | Assunpink Creek | +----------+-------------+-----------------+
The first character of the LOC code indicates the record type, i.e. A is Archive, S id Subject File, M is Manuscript files. Consider (note that SQL response has been hand-edited to include code meanings):
mysql> select distinct substr(loc,1,2) from item; +-----------------+ | substr(loc,1,2) | +-----------------+ | W | | S - Subject | | M - Manuscript | | L0 - Slides | | C - Maps | | P - Poster | | N - Negative | | R | | I | | F - Artifact | | A - Archive | | B - Book | | AV | | TA - Audio Tape | | AH | | E | | D | | U | | TV - Video Tape | | B0 - input error| | S0 - input error| | CD - Map Drawer | | A0 - input error| | F0 - input error| +-----------------+ 24 rows in set (0.03 sec)
One can see an imperfect correspondence between the above codes and the type table keys. The discrepancies are probably just due to some data entry errors in the loc field.
Noodling
Joining place with item on loc seems to yield reasonable results. For example:
mysql> select i.loc,place,replace(substr(comments,1,80),'\n',' ') from item i, place p where i.loc = p.loc and place regexp 'valley forge'; +-------------+---------------------+----------------------------------------------------------------------------------+ | loc | place | replace(substr(comments,1,80),'\n',' ') | +-------------+---------------------+----------------------------------------------------------------------------------+ | P001:01-04 | Valley Forge Avenue | NULL | | P001:01-05 | Valley Forge Avenue | Photographs showing intersection of Brunswick Avenue with Carr Avenue and Valle | | S023:02-02 | Valley Forge Avenue | News clips. June 1994 sees changes in the neighborhood that will improve | | S003:02-01 | Valley Forge Avenue | Neighborhood bounded by City of Trenton on the south (near Strawberry Street fr | | S023:02-02 | Valley Forge Avenue | News clips. File includes tax maps showing tax lots, streets, the canal a | | S023:03-03 | Valley Forge Avenue | File contains manuscript entitled "Slackwood, My Community" written by Ja | | A016:01-06 | Valley Forge Avenue | News clips. See file: A002: 07-01,-02, and -03 for site plan informa | | A002:09-12 | Valley Forge Avenue | Tiffany Woods receives a six month extension to sell and complete house si | | S023:03-03 | Valley Forge Avenue | File contains manuscript entitled "Slackwood, My Community" written by Jack Ell | | CD08:14- | Valley Forge Avenue | Mercer County subdivision map no. 299-B. | | S031:01-01 | Valley Forge Avenue | NULL | | A018:04-07 | Valley Forge Avenue | News clips. Council introduces and ordinance in February 2001 to extend J | +-------------+---------------------+----------------------------------------------------------------------------------+
Joining with type according to loc prefix yields equally reasonable results:
mysql> select i.loc,place,t.descrip,replace(substr(comments,1,40),'\n',' ') from item i, place p, type t where i.loc = p.loc and code = substr(i.loc,1,2) and place regexp 'valley forge'; +-------------+---------------------+--------------+------------------------------------------+ | loc | place | descrip | replace(substr(comments,1,40),'\n',' ') | +-------------+---------------------+--------------+------------------------------------------+ | P001:01-04 | Valley Forge Avenue | Photographs | NULL | | P001:01-05 | Valley Forge Avenue | Photographs | Photographs showing intersection of Brun | | S003:02-01 | Valley Forge Avenue | Subject File | Neighborhood bounded by City of Trenton | | S023:02-02 | Valley Forge Avenue | Subject File | News clips. June 1994 sees changes | | S023:02-02 | Valley Forge Avenue | Subject File | News clips. File includes tax maps | | S023:03-03 | Valley Forge Avenue | Subject File | File contains manuscript entitled | | A016:01-06 | Valley Forge Avenue | Archives | News clips. See file: A002: 0 | | CD08:14- | Valley Forge Avenue | Maps | Mercer County subdivision map no. 2 | | A002:09-12 | Valley Forge Avenue | Archives | Tiffany Woods receives a six month | | S023:03-03 | Valley Forge Avenue | Subject File | File contains manuscript entitled "Slack | | S031:01-01 | Valley Forge Avenue | Subject File | NULL | | A018:04-07 | Valley Forge Avenue | Archives | News clips. Council introduces and | +-------------+---------------------+--------------+------------------------------------------+
Plans
There is a crude front-end to this database that enables the public to search for information in limited ways. It needs further development.
We should build a data dictionary, or rather, simply document the meaning of the fields in the item table, and the precise semantics of the loc field.
