Schema - CMS Metadata
- Jeffrey Campbell
This table schema will be used to create unit-specific tables, i.e. a separate database table that can be permissioned specifically for a given unit (YUAG, YCBA, YPM) so that the unit can safely bulk update data in the table without affecting other units. The assumption is that these will be named “yuag_cms_metadata”, “ycba_cms_metadata” and “ypm_cms_metadata”.
- 1 Change Log
- 2 Schema Summary
- 3 Schema Column Details
- 3.1 cmstype (Composite Primary Key)
- 3.2 cmsid (Composite Primary Key)
- 3.3 lastmodified
- 3.4 cmschecksum
- 3.5 cmsnumber
- 3.6 altcmsnumbers
- 3.7 title
- 3.8 artists
- 3.9 beginyear
- 3.10 endyear
- 3.11 begindate
- 3.12 enddate
- 3.13 datestring
- 3.14 department
- 3.15 cmskeywords
- 3.16 classification
- 3.17 culture
- 3.18 medium
- 3.19 creditline
- 3.20 creditlinerepro
- 3.21 cmscopyrightstatus
- 3.22 copyrightstatement
- 3.23 location
- 3.24 updateRequested
Change Log
Derived from: CMS Metadata Schema Google doc
Document modified | By | Description |
Wednesday 2017-10-25 | Tim Speevack | Combined sheet and doc, fixed some column length inconsistencies. |
Tuesday, 2017-11-7 | Tim Speevack | Added location column for exhibitions mapping |
Wednesday, 2018-01-31 | Tim Speevack | Renamed checksum to cmschecksum Renamed copyrightstatus cmscopyrightstatus Renamed keywords to cmskeywords |
Friday, 2018-8-3 | Youjun Guo | Add column updateRequested |
Friday, 2022-08-266 | Jeffrey Campbell | Began adding PostgreSQL data types |
Friday, 2023 -01-06 | Jeffrey Campbell | Migrated from Google Doc |
Schema Summary
Column Name SQL Server | Datatype SQL Server | Column Name PostgreSQL (if different) | Datatype PostgreSQL | Nullable | Notes | Is Attribute? |
|---|---|---|---|---|---|---|
cmstype | nchar(3) |
| nchar | not null | Primary Key |
|
cmsid | integer |
| integer | not null | Primary Key |
|
lastmodified | datetime |
| timestamp(3) | not null |
|
|
cmschecksum | varbinary(20) |
| bytea | null |
|
|
cmsnumber | nvarchar(60) |
|
| not null |
| R/O |
altcmsnumbers | nvarchar(400) |
|
| null |
| R/O |
title | nvarchar(2000) |
|
| null |
| R/O |
artists | nvarchar(max) |
| text | null |
| R/O |
beginyear | integer |
| integer | null |
| R/O |
endyear | integer |
| integer | null |
| R/O |
begindate | date |
| date | null |
| R/O |
enddate | date |
| date | null |
| R/O |
datestring | nvarchar(200) |
|
| null |
| R/O |
department | nvarchar(100) |
|
| null |
| R/O |
cmskeywords | nvarchar(max) |
|
| null |
| R/O |
classification | nvarchar(2000) |
|
| null |
| R/O |
culture | nvarchar(100) |
|
| null |
| R/O |
medium | nvarchar(2000) |
|
| null |
| R/O |
creditline | nvarchar(2000) |
|
| null |
| R/O |
creditlinerepro | nvarchar(2000) |
|
| null |
| R/O |
cmscopyrightstatus | nvarchar(256) |
|
| null |
| R/O |
copyrightstatement | nvarchar(1000) |
|
| null |
| R/O |
location | nvarchar(200) |
|
| null |
| R/O |
updateRequested | smallint |
| smallint | null |
| R/W |
Schema Column Details
cmstype (Composite Primary Key) | |
Datatype | nchar(3) |
Nullable | No |
Description | String which identifies the source cms system at the unit level. |
Notes | Contains short unit-specific values which identify the cms source which, combined with the cmsid, will uniquely identify the parent record in the unit’s CMS system. |
Sample value | obj, exb, evt, lec, doc |
cmsid (Composite Primary Key) | |
Datatype | integer |
Nullable | No |
Description | Integer which when combined with the cmstype will uniquely identify the original source record in the unit’s CMS system. |
Notes | For YUAG this would be the TMS objectID or exhibitionID, or an id from a custom events database. For YPM this would be the integer identifier of the object in the EMu catalogue module. |
Sample value | 12345 |
lastmodified | |
Datatype | datetime |
Nullable | No |
Description | Timestamp of the last modification of this cms record |
Notes | This will defatult to getDate() on insert, then updated by the unit nightly when cms data is updated from the unit’s cms system(s). |
Sample value |
|
cmschecksum | |
Datatype | varbinary(20) |
Nullable | Yes |
Description | Optional field used solely by the units to store a checksum of the cms data for change management purposes. |
Notes | This field will be used by YUAG to store the result of sql Hashbytes(‘MD5’,<list of columns>). The generation of this value is done on the unit side. This value is compared to the record in the cms system and if different it indicates that at least one of the values has changed. The list of columns will be all fields below this, i.e. cmsnumber through copyrightstatement. |
Sample value |
|
cmsnumber | |
Datatype | nvarchar(60) |
Nullable | N |
Description | A string which is unique to the record in the CMS system. |
Notes | For TMS this would be the accession number. For EMu this will be the catalog number of the object. In TMS this has a native length of 42 characters. In EMu catalog numbers currently conform to AAA.NNNNNN.aaa where AAA is the departmental prefix, NNNNNN is a six-digit zero-padded integer, and aaa is an optional suffix (usually .001, .002 but legacy .A .B etc. exist) |
Sample value | 2012.10.1-2, or ENT.406573 |
altcmsnumbers | |
Datatype | nvarchar(400) |
Nullable | Yes |
Description | Presumed to be a delimited list of alternate identifiers for the item |
Notes | At YUAG this would be a string like “AS2000.8.16a,b, TR2000.12242.1,.2, etc.” Largest current value at YUAG would be 163 characters, though this could grow to be larger. |
Sample value | ILE2010.1.263,PD2007.66.1-.750,PD2007.66.1-.754,PD2007.66.1-.758,TR2007.13842.1-.717,TR2007.13842.1-.758,TR2007.13842.308.1,TR2007.13842.493 |
title | |
Datatype | nvarchar(2000) |
Nullable | Yes |
Description | Intended for the title of the object, exhibition or other CMS entity |
Notes | YPM specimens and anthropological artifacts have no formal title in the same sense that e.g. works of art have, but suitable concatenations have been in place for several years for delivery to CCD. For example, a title for a butterfly might be “ENT.123456: Papilio glaucus. USA: CT: New Haven County, 20 July 2005, coll. John Doe” |
Sample value |
|
MM Mapping | YALE_COMMON_FIELDS.TITLE nvarchar(4096). Current max is 1754 |
artists | |
Datatype | nvarchar(max) |
Nullable | Yes |
Description | Designed to contain a delimited string of names associated with the CMS object for search purposes. |
Notes | The maximum value at YUAG for this field would be an object which has 162 creators totalling 4502 characters as a comma separated list. Field is not applicable to YPM unless it opted to overload “collectors” here, which would only rarely imply a string as long as 100-200 characters total. This field is not intended to be parseable, and no consistency in delimiters is imposed, i.e. it could be delimited by comma, space, pipe or any other character. |
Sample value | “Tripolis (Lydia),Unknown,Augustus, Emperor of Rome” |
beginyear | |
Datatype | integer |
Nullable | Yes |
Description | Optional begin year value for the range of dates associated with this CMS entity |
Notes | Contains a year value which maps to the TMS begin year. This field uses negative numbers for B.C. years. Probably won’t be used by any unit other than YUAG. |
Sample value | -110 (for 110 B.C.) |
MM Mapping | YALE_COMMON_FIELDS.BEGIN_YEAR integer |
endyear | |
Datatype | integer |
Nullable | Yes |
Description | End year value for the range of dates associated with this CMS entity |
Notes | <same as above> |
Sample value |
|
MM Mapping | YALE_COMMON_FIELDS.END_YEAR integer |
begindate | |
Datatype | date |
Nullable | Yes |
Description | SQL date field for the begin date associated with this CMS entity |
Notes | This is an alternative to the begin/end year values where B.C. dates are not required. This would be used for exhibitions and events, for example, where more current dates would apply. YPM specimens and artifacts will have begindates and/or enddates. YUAG will populate this with 1/1/year or 12/31/year when the date range falls into the sql date type range. |
enddate | |
Datatype | date |
Nullable | Yes |
Description | SQL date field for the end date associated with this CMS entity |
Notes | <same as above> |
datestring | |
Datatype | nvarchar(200) |
Nullable | Yes |
Description | An alternative date field, typically used for descriptive date string |
Notes | At YPM, applicable to the Anthropology and Historical Scientific Instruments holdings only. At YUAG this won’t be particularly useful for searching due to data inconsistencies, but we’ll include it anyway. |
Sample value | “ca. 1775–1848”, “1295-1186 B.C.”, or “mid 14th century” |
MM Mapping | YALE_COMMON_FIELDS.DATE nvarchar(510), max length currently is 94 characters |
department | |
Datatype | nvarchar(100) |
Nullable | Yes |
Description | String describing within-unit ownership/stewardship of the object |
Notes |
|
Sample value | “Ancient Art” or “Photography” or “Vertebrate Paleontology” |
MM Mapping | YALE_COMMON_FIELDS.DEPARTMENT nvarchar(510), max length currently is 79 characters |
cmskeywords | |
Datatype | nvarchar(max) |
Nullable | Yes |
Description | Keywords related to the CMS record (not the media asset). |
Notes |
|
Sample value |
|
MM Mapping | YALE_COMMON_FIELDS.KEYWORDS nvarchar(4096), max length currently is 648 characters. |
classification | |
Datatype | nvarchar(2000) |
Nullable | Yes |
Description | Delimited text representing classifications from the CMS system |
Notes | We decided a single classification field was sufficient (and would store CSV or other concatenated data from the source cms) and that values would differ between units (no attempt to unify values across units). |
Sample value |
|
culture | |
Datatype | nvarchar(100) |
Nullable | Yes |
Description |
|
Notes | Contains values like “Roman, Syria” and “Mexico, Maya, possibly Toltec” |
Sample value |
|
MM Mapping | YALE_CULTURAL_OBJS nvarchar(2048), max length currently 64 characters. |
medium | |
Datatype | nvarchar(2000) |
Nullable | Yes |
Description | Contains values like “Gelatin silver print” or “coral; stone” (e.g., for materials for YPM Anthropology). At YUAG these can be very verbose. |
Notes |
|
Sample value |
|
MM Mapping | YALE_COMMON_FIELDS.MEDIUM nvarchar(4096), max length currently is 1444 characters. |
creditline | |
Datatype | nvarchar(2000) |
Nullable | Yes |
Description | Intended for storage of item related credit (donor, for example) |
Notes |
|
Sample value |
|
MM Mapping | YALE_COMMON_FIELDS.CREDIT_LINE nvarchar(4096), max length currently is 1072 characters. |
creditlinerepro | |
Datatype | nvarchar(2000) |
Nullable | Yes |
Description | Contains values like “Yale University Art Gallery” |
Notes |
|
Sample value |
|
MM Mapping | YALE_COMMON_FIELDS.CREDIT_LINE_REP nvarchar(4096), max length currently is 724 characters |
cmscopyrightstatus | |
Datatype | nvarchar(256) |
Nullable | Yes |
Description | Contains values like “public domain” or “under copyright” |
Notes |
|
Sample value |
|
MM Mapping | YALE_COMMON_FIELDS.COPY_STATUS_WORK nvarchar(256), max length currently is 58 characters |
copyrightstatement | |
Datatype | nvarchar(1000) |
Nullable | Yes |
Description | Contains copyright statement for the work, like “Copyright Artist/Estate/Foundation” |
Notes |
|
Sample value |
|
MM Mapping | YALE_COMMON_FIELDS.COPY_BYLINE_WORK nvarchar(2048), max length currently is 321 characters |
location | |
Datatype | nvarchar(200) |
Nullable | Yes |
Description | Primarily intended for use with exhibition records, this field will contain descriptive location information for the event/exhibition (or object, if desired) |
Notes | YUAG will most often use this field for event/lecture/documentation media locations. |
Sample value | “Kahn 3, Sculpture Courtyard” |
MM Mapping | YALE_EVENTS_PLACE_EXHIB.PLACE, nvarchar(255), max length in MM is 27 characters. |
updateRequested | |
Datatype | smallint |
Nullable | Yes |
Description | Departments set this flag to 1 to indicate there is/are cms change need to push to NetX |
Notes | TMAN will implement the push and turn the flag to 0 when finish |
Sample value | 0 or 1 |
MM Mapping | n/a |