Here's the current database structure:
mysql> show columns from allColls;
| Field | Type | Null | Key | Default | Extra |
| dnum | int(30) | NO | PRI | NULL | auto_increment |
| id_2009 | varchar(60) | NO | | NULL | |
| title | varchar(255) | YES | | NULL | |
| listLevel | int(4) | YES | | NULL | |
| parentID | varchar(60) | YES | | NULL | |
| sourceCollName | varchar(255) | YES | | NULL | |
| mssNum | varchar(15) | YES | | NULL | |
| mssUrl | varchar(80) | YES | | NULL | |
| blurb | blob | YES | MUL | NULL | |
| type | varchar(30) | YES | | NULL | |
| alphaBy | varchar(100) | YES | | NULL | |
| AnalogOrDigital | char(1) | YES | | NULL | |
| online | char(1) | YES | | NULL | |
| inAcumen | char(1) | YES | | NULL | |
| iconLocation | varchar(255) | YES | | NULL | |
| dateLive | date | YES | | NULL | |
| cannedLink | varchar(255) | YES | | NULL | |
| PURL | varchar(255) | YES | | NULL | |
| notes | text | YES | | NULL | |
| physicalLocation | varchar(250) | YES | | NULL | |
| analogQuantity | varchar(125) | YES | | NULL | |
| primaryAnalogFormat | varchar(250) | YES | | NULL | |
| donorID | int(11) | YES | | NULL | |
| processingFunderID | int(11) | YES | | NULL | |
| digitizationFunderID | int(11) | YES | | NULL | |
| accessRightsID | int(11) | YES | | NULL | |
| usageRightsID | int(11) | YES | | NULL | |
| preservationRightsID | int(11) | YES | | NULL | |
| whoCanAccessThisID | int(11) | YES | | NULL | |
| whenCanItBeAccessedID | int(11) | YES | | NULL | |
| collectionSubmittedBy | int(11) | YES | | NULL | |
| metadataLead | int(11) | YES | | NULL | |
| digitizationLead | int(11) | YES | | NULL | |
| metadataStatus | char(1) | YES | | NULL | |
| digitizationStatus | char(1) | YES | | NULL | |
| dateAddedToQueue | date | YES | | NULL | |
| dateMetadataCompleted | date | YES | | NULL | |
| dateDigitizationCompleted | date | YES | | NULL | |
33 rows in set (0.00 sec)
The current query that pulls information for our collection browse page is:
"SELECT title, iconLocation, blurb, online, cannedLink, mssUrl, id_2009, listLevel FROM InfoTrack.allColls WHERE alphaBy LIKE '".$sortVal."%' AND online=1 AND listLevel=1 ORDER BY alphaBy LIMIT ".($page ? ($page*$_SESSION['max']).', ' : ).$_SESSION['max'].";"
-- where the LIMIT statement is PHP checking Session variables for the pagination feature.
This is our primary table for information about our collections, and as such, it is frequently altered and updated. The first entry of information here generally comes from the Collection_Information XML file created by Digital Services during digitization.
- dnum is simply an auto-incrementing number to serve as primary key.
- id_2009 is the identifier for this collection assigned according to our 2009 File_naming_schemes
- title is the title of the collection
- listLevel is currently either 1 (one) indicating it should appear in the online collection list, or NULL (it should not)
- parentID is the id_2009 - type identifier for the analog collection from which this digital collection was derived. This only applies to digital collections, and the analog collection may or may not be listed in this database.
- sourceCollName is the title of the analog collection from which this collection was derived.
- mssNum is the manuscript number of this collection, if applicable, in the form: MS xxxx where xxxx is left-padded with zeros to 4 places. For the u0003 (manuscript) collections, this number corresponds to the second set of numbers in the id_2009 identifier. That is to say, u0003_0000252 will have an mssNum of MS 0252.
- mssUrl is the link to the online finding aid from which this digital collection was derived. This link should NOT be entered in analog collections, as their URL will go into the cannedLink field instead.
- blurb is the description of this collection, most frequently derived from the abstract in the finding aid.
- type tells us what kind of icon to assign to this content, and how to count it in ARL preservation statistics. Currently supported options are: book, image, text, audio, video, mixed media, finding aid, score, other.
- alphaBy provides the information as to how to alphabetize this title. If, for example this is "John Smith's Papers", this value will likely be something like "Smith, John." From the EAD finding aids, this information is pulled from the "filing title" entry.
- AnalogOrDigital contains either "D" for digital, or "A" for analog. The latter is used for finding aids, or simply for reference to the manuscript collection.
- online equals 1 (one) if true, and NULL otherwise.
- inAcumen equals 1 (one) if this content is in Acumen, NULL otherwise.
- iconLocation contains the URL to the icon assigned for this collection. If no icon was supplied, the icon assigned is based on the value of the type field above.
- dateLive is a yyyy-mm-dd form of date to indicate when this collection went live on the web. Early collections are estimated dates only.
- cannedLink is a URL to the collection online
- PURL is persistent URL to online display
- notes is a free-text field for additional information of any sort.
- physicalLocation is where the analog content resides, such as Williams shelf location
- analogQuantity is how much analog there is, usually in linear feet
- primaryAnalogFormat may be something like "ledgers" or "lantern slides" or "negatives"
- donorID refers to the donor of the content; see collectionRelatedNames table
- processingFunderID refers to the processing funder; see collectionRelatedNames table
- digitizationFunderID -- who funded the digitization?
- accessRightsID references ID in accessRights table for access rights statement
- usageRightsID references ID in usageRights table for usage rights statement
- preservationRightsID references ID in preservationRights for preservation rights status
- whoCanAccessThisID references ID in whoCanAccessThis to indicate whether it's open to everyone, UA only, or reading room access only
- whenCanItBeAccessedID references ID in whenCanItBeAccessed with startDate (and possibly endDate) for things that we can't yet provide access to-- but WILL --and need to digitize now. Leave blank if not needed.
- collectionSubmittedBy -- who put this in the queue for digitization
- metadataLead -- who's in charge of the metadata?
- digitizationLead -- and who's in charge of the digitization?
- metadataStatus -- N = None, I = In progress, C = Completed, H = on Hold
- digitizationStatus -- N = None, I = In progress, C = Completed, H = on Hold
- dateAddedToQueue -- yyyy-mm-dd
- dateMetadataCompleted -- yyyy-mm-dd
- dateDigitizationCompleted -- yyyy-mm-dd
--Jlderidder 09:05, 14 December 2012 (CST)