Re: [Refdb-users] call numbers

[Bruce D'A. <bd...@fa...>]

Don't know if this is useful or not, but here's how the new project 
LibDB handles this stuff. The project is starting as a movie db (hence 
the "DVD Shelf" and such), but is being designed to become much more 
extensive over time.

-- 
----------------------------------------------------------------------- 
--

CREATE TABLE libdb_locations ( id CHAR(20) NOT NULL, item_id CHAR(20) 
NOT NULL, location_type_id CHAR(20) NOT NULL ) TYPE=MyISAM;
INSERT INTO libdb_help VALUES ( "libdb_locations", "id",                
  "en", "A unique 20 character alphanumeric ID." );
INSERT INTO libdb_help VALUES ( "libdb_locations", "item_id",           
  "en", "The unique 20 character identifier of the item." );
INSERT INTO libdb_help VALUES ( "libdb_locations", "location_type_id",  
  "en", "The unique identifier that describes this location; see 
locations_types." );

-- 
----------------------------------------------------------------------- 
--

CREATE TABLE libdb_locations_types ( id CHAR(20) NOT NULL PRIMARY KEY, 
name VARCHAR(100) NOT NULL, description TINYTEXT ) TYPE=MyISAM;
INSERT INTO libdb_help VALUES ( "libdb_locations_types", "id",          
"en", "A unique 20 character alphanumeric ID." );
INSERT INTO libdb_help VALUES ( "libdb_locations_types", "name",        
"en", "A brief, but recognizable, name for this location \(e.g., DVD 
Shelf 1, 2nd. Floor, Box 13, etc.\). ");
INSERT INTO libdb_help VALUES ( "libdb_locations_types", "description", 
"en", "A verbose explanation about this type of location \(e.g., 
specific directions, etc.\)." );
INSERT INTO libdb_locations_types VALUES ( "wIINYBlgzIEe4RukLR6G", "DVD 
Shelf 1", "All DVDs on this shelf are organized alphabetically, and are 
spine up." );
INSERT INTO libdb_locations_types VALUES ( "tx8D0PodmXdY0IjGx5PI", "VHS 
Shelf 1", "All VHS tapes on this shelf are organized alphabetically, 
save for oversized items, and are spine up." );

-- 
----------------------------------------------------------------------- 
--

CREATE TABLE libdb_identifiers ( id CHAR(20) NOT NULL, referer_table 
VARCHAR(50) NOT NULL, referer_id CHAR(20) NOT NULL, identifier_type_id 
CHAR(20) NOT NULL, identifier TINYTEXT ) TYPE=MyISAM;
INSERT INTO libdb_help VALUES ( "libdb_identifiers", "id",              
    "en", "A unique 20 character alphanumeric ID." );
INSERT INTO libdb_help VALUES ( "libdb_identifiers", "referer_table",   
    "en", "The name of the table doing the referring." );
INSERT INTO libdb_help VALUES ( "libdb_identifiers", "referer_id",      
    "en", "The unique 20 character identifier of the referring table." 
);
INSERT INTO libdb_help VALUES ( "libdb_identifiers", 
"identifier_type_id", "en", "The unique identifier that describes this 
identifier; see identifiers_types." );
INSERT INTO libdb_help VALUES ( "libdb_identifiers", "identifier",      
    "en", "The identifier itself, either numbers, a word or phrase, or a 
combination of both." );

-- 
----------------------------------------------------------------------- 
--

CREATE TABLE libdb_identifiers_types ( id CHAR(20) NOT NULL PRIMARY 
KEY, name VARCHAR(100) NOT NULL, description TINYTEXT NOT NULL ) 
TYPE=MyISAM;
INSERT INTO libdb_help VALUES ( "libdb_identifiers_types", "id",        
   "en", "A unique 20 character alphanumeric ID." );
INSERT INTO libdb_help VALUES ( "libdb_identifiers_types", "name",      
   "en", "A brief, but recognizable, name for this relationship \(e.g., 
Amazon ASIN, IMDb, etc.\). ");
INSERT INTO libdb_help VALUES ( "libdb_identifiers_types", 
"description", "en", "A verbose explanation about this type of 
identifier." );
INSERT INTO libdb_identifiers_types VALUES ( "gpREVQortpEnex1Zvr50", 
"Artisan Home Entertainment Cat. No.", "The Artisan Home Entertainment 
Catalog Number is displayed in the UPC box of their DVD purchases." );
INSERT INTO libdb_identifiers_types VALUES ( "CVxByT1K0a2lcfMVi0j4", 
"ASIN", "ASIN stands for Amazon Standard Identification Number. Almost 
every product on our site has its own ASIN -- a unique code we use to 
identify it. For books, the ASIN is the same as the ISBN number, but 
for all other products a new ASIN is created when the item is uploaded 
to our catalogue." );
INSERT INTO libdb_identifiers_types VALUES ( "1H7tVsmrA1mbrxQWTKfA", 
"DOI",  "DOIs are names \(characters and/or digits\) assigned to 
objects of intellectual property \(physical, digital or abstract\) such 
as electronic journal articles, images, learning objects, ebooks, 
images, any kind of content. They are used to provide current 
information, including where they \(or information about them\) can be 
found on the Internet. Information about a digital object may change 
over time, including where to find it, but its DOI will not change." );
INSERT INTO libdb_identifiers_types VALUES ( "LsDwzmSpSTXf5poXzSIh", 
"Fingerprint", "A fingerprint is an identifier constructed by combining 
groups of characters transcribed from specified pages of a printed 
item.  The technique is used primarily to signal differences between 
individual copies of early printed books." );
INSERT INTO libdb_identifiers_types VALUES ( "lJT0NNFWvPNUHPFU35PA", 
"IMDb", "The Internet Movie Database uses a nine-digit string to 
identify its resources; the first two characters determine whether it's 
a movie title or person's name." );
INSERT INTO libdb_identifiers_types VALUES ( "s7dzo7xsxeAN4WD9Jh1y", 
"ISSN", "The ISSN \(International Standard Serial Number\) is an 
eight-digit number which identifies periodical publications as such, 
including electronic serials. More than one million ISSN numbers have 
so far been assigned." );
INSERT INTO libdb_identifiers_types VALUES ( "xhC5cUEseR208u4jkKGV", 
"ISBN", "The ISBN is a unique machine-readable identification number, 
which marks any book unmistakably. For 30 years the ISBN has 
revolutionized the international book-trade. 159 countries and 
territories are officially ISBN members." );
INSERT INTO libdb_identifiers_types VALUES ( "crKg5OznuFZ1A6U6kS28", 
"LCCN", "The Library of Congress began to print catalog cards in 1898 
and began to distribute them in 1901. The Library of Congress Card 
Number was the number used to identify and control catalog cards." );
INSERT INTO libdb_identifiers_types VALUES ( "ks8iH6UoVugsgc9btI9c", 
"UPC Type A", "The basic UPC code, referred to as Type A, is composed 
of twelve digits. These twelve digits are broken up into four groups: 
Number System Character, Manufacturer's Code, Product Code, and Check 
Digit." );
INSERT INTO libdb_identifiers_types VALUES ( "ZhBU2tsUYphad23MTl9a", 
"UPC Type E", "All Type-E UPC codes are eight digits long, the first 
being the Number System Character and the last being the check digit. 
The number system character is ALWAYS ZERO. The check digit is 
calculated from the digits in the Type-A UPC." );