From 98ab1220d846cde1dad34bcd0af508d49ecf70db Mon Sep 17 00:00:00 2001 From: tonnesen Date: Wed, 29 May 2002 21:11:41 +0000 Subject: [PATCH] Further simplifications to the marc schema specification --- marc/marcschema.sql | 250 +++++--------------------------------------- 1 file changed, 27 insertions(+), 223 deletions(-) diff --git a/marc/marcschema.sql b/marc/marcschema.sql index 9556196a82..a5ab183d20 100644 --- a/marc/marcschema.sql +++ b/marc/marcschema.sql @@ -1,3 +1,6 @@ + +# These first three tables store the data from a MARC record. + # marc_biblio contains 1 record for each biblio in the DB CREATE TABLE marc_biblio ( bibid bigint(20) unsigned NOT NULL auto_increment, @@ -9,216 +12,19 @@ ) TYPE=MyISAM; -# marc_NXX_tag_table contains 1 record for each tag of every biblio - - CREATE TABLE marc_0XX_tag_table ( - tagid bigint(20) unsigned NOT NULL auto_increment, - bibid bigint(20) NOT NULL default '0', - tagnumber char(3) NOT NULL default '', - tagorder tinyint(4) NOT NULL default '0', - indicator char(2) NOT NULL default '', - PRIMARY KEY (tagid), - KEY (bibid,tagnumber,tagorder) - ) TYPE=MyISAM; - CREATE TABLE marc_1XX_tag_table ( - tagid bigint(20) unsigned NOT NULL auto_increment, - bibid bigint(20) NOT NULL default '0', - tagnumber char(3) NOT NULL default '', - tagorder tinyint(4) NOT NULL default '0', - indicator char(2) NOT NULL default '', - PRIMARY KEY (tagid), - KEY (bibid,tagnumber,tagorder) - ) TYPE=MyISAM; - CREATE TABLE marc_2XX_tag_table ( - tagid bigint(20) unsigned NOT NULL auto_increment, - bibid bigint(20) NOT NULL default '0', - tagnumber char(3) NOT NULL default '', - tagorder tinyint(4) NOT NULL default '0', - indicator char(2) NOT NULL default '', - PRIMARY KEY (tagid), - KEY (bibid,tagnumber,tagorder) - ) TYPE=MyISAM; - CREATE TABLE marc_3XX_tag_table ( - tagid bigint(20) unsigned NOT NULL auto_increment, - bibid bigint(20) NOT NULL default '0', - tagnumber char(3) NOT NULL default '', - tagorder tinyint(4) NOT NULL default '0', - indicator char(2) NOT NULL default '', - PRIMARY KEY (tagid), - KEY (bibid,tagnumber,tagorder) - ) TYPE=MyISAM; - CREATE TABLE marc_4XX_tag_table ( - tagid bigint(20) unsigned NOT NULL auto_increment, - bibid bigint(20) NOT NULL default '0', - tagnumber char(3) NOT NULL default '', - tagorder tinyint(4) NOT NULL default '0', - indicator char(2) NOT NULL default '', - PRIMARY KEY (tagid), - KEY (bibid,tagnumber,tagorder) - ) TYPE=MyISAM; - CREATE TABLE marc_5XX_tag_table ( - tagid bigint(20) unsigned NOT NULL auto_increment, - bibid bigint(20) NOT NULL default '0', - tagnumber char(3) NOT NULL default '', - tagorder tinyint(4) NOT NULL default '0', - indicator char(2) NOT NULL default '', - PRIMARY KEY (tagid), - KEY (bibid,tagnumber,tagorder) - ) TYPE=MyISAM; - CREATE TABLE marc_6XX_tag_table ( - tagid bigint(20) unsigned NOT NULL auto_increment, - bibid bigint(20) NOT NULL default '0', - tagnumber char(3) NOT NULL default '', - tagorder tinyint(4) NOT NULL default '0', - indicator char(2) NOT NULL default '', - PRIMARY KEY (tagid), - KEY (bibid,tagnumber,tagorder) - ) TYPE=MyISAM; - CREATE TABLE marc_7XX_tag_table ( - tagid bigint(20) unsigned NOT NULL auto_increment, - bibid bigint(20) NOT NULL default '0', - tagnumber char(3) NOT NULL default '', - tagorder tinyint(4) NOT NULL default '0', - indicator char(2) NOT NULL default '', - PRIMARY KEY (tagid), - KEY (bibid,tagnumber,tagorder) - ) TYPE=MyISAM; - CREATE TABLE marc_8XX_tag_table ( - tagid bigint(20) unsigned NOT NULL auto_increment, - bibid bigint(20) NOT NULL default '0', - tagnumber char(3) NOT NULL default '', - tagorder tinyint(4) NOT NULL default '0', - indicator char(2) NOT NULL default '', - PRIMARY KEY (tagid), - KEY (bibid,tagnumber,tagorder) - ) TYPE=MyISAM; - CREATE TABLE marc_9XX_tag_table ( - tagid bigint(20) unsigned NOT NULL auto_increment, - bibid bigint(20) NOT NULL default '0', - tagnumber char(3) NOT NULL default '', - tagorder tinyint(4) NOT NULL default '0', - indicator char(2) NOT NULL default '', - PRIMARY KEY (tagid), - KEY (bibid,tagnumber,tagorder) - ) TYPE=MyISAM; - - -# marc_NXX_subfield_table contains 1 record for each subfield of every tag if -# the subfield value is more than 255 length, the value is in the marc_blob_tag -# table, the valuebloblink contains the number of the blob - - CREATE TABLE marc_0XX_subfield_table ( - subfieldid bigint(20) unsigned NOT NULL auto_increment, - tagid bigint(20) NOT NULL default '0', - bibid bigint(20) NOT NULL default '0', - subfieldorder tinyint(4) NOT NULL default '0', - subfieldcode char(1) NOT NULL default '', - subfieldvalue varchar(255) default NULL, - valuebloblink bigint(20) default NULL, - PRIMARY KEY (subfieldid), - KEY (bibid,tagid,subfieldcode) - ) TYPE=MyISAM; - CREATE TABLE marc_1XX_subfield_table ( +CREATE TABLE marc_subfield_table ( subfieldid bigint(20) unsigned NOT NULL auto_increment, tagid bigint(20) NOT NULL default '0', + tag char(3) NOT NULL default '', bibid bigint(20) NOT NULL default '0', subfieldorder tinyint(4) NOT NULL default '0', - subfieldcode char(1) NOT NULL default '', + subfieldmark char(1) NOT NULL default '', subfieldvalue varchar(255) default NULL, valuebloblink bigint(20) default NULL, PRIMARY KEY (subfieldid), - KEY (bibid,tagid,subfieldcode) - ) TYPE=MyISAM; - CREATE TABLE marc_2XX_subfield_table ( - subfieldid bigint(20) unsigned NOT NULL auto_increment, - tagid bigint(20) NOT NULL default '0', - bibid bigint(20) NOT NULL default '0', - subfieldorder tinyint(4) NOT NULL default '0', - subfieldcode char(1) NOT NULL default '', - subfieldvalue varchar(255) default NULL, - valuebloblink bigint(20) default NULL, - PRIMARY KEY (subfieldid), - KEY (bibid,tagid,subfieldcode) - ) TYPE=MyISAM; - CREATE TABLE marc_3XX_subfield_table ( - subfieldid bigint(20) unsigned NOT NULL auto_increment, - tagid bigint(20) NOT NULL default '0', - bibid bigint(20) NOT NULL default '0', - subfieldorder tinyint(4) NOT NULL default '0', - subfieldcode char(1) NOT NULL default '', - subfieldvalue varchar(255) default NULL, - valuebloblink bigint(20) default NULL, - PRIMARY KEY (subfieldid), - KEY (bibid,tagid,subfieldcode) - ) TYPE=MyISAM; - CREATE TABLE marc_4XX_subfield_table ( - subfieldid bigint(20) unsigned NOT NULL auto_increment, - tagid bigint(20) NOT NULL default '0', - bibid bigint(20) NOT NULL default '0', - subfieldorder tinyint(4) NOT NULL default '0', - subfieldcode char(1) NOT NULL default '', - subfieldvalue varchar(255) default NULL, - valuebloblink bigint(20) default NULL, - PRIMARY KEY (subfieldid), - KEY (bibid,tagid,subfieldcode) - ) TYPE=MyISAM; - CREATE TABLE marc_5XX_subfield_table ( - subfieldid bigint(20) unsigned NOT NULL auto_increment, - tagid bigint(20) NOT NULL default '0', - bibid bigint(20) NOT NULL default '0', - subfieldorder tinyint(4) NOT NULL default '0', - subfieldcode char(1) NOT NULL default '', - subfieldvalue varchar(255) default NULL, - valuebloblink bigint(20) default NULL, - PRIMARY KEY (subfieldid), - KEY (bibid,tagid,subfieldcode) - ) TYPE=MyISAM; - CREATE TABLE marc_6XX_subfield_table ( - subfieldid bigint(20) unsigned NOT NULL auto_increment, - tagid bigint(20) NOT NULL default '0', - bibid bigint(20) NOT NULL default '0', - subfieldorder tinyint(4) NOT NULL default '0', - subfieldcode char(1) NOT NULL default '', - subfieldvalue varchar(255) default NULL, - valuebloblink bigint(20) default NULL, - PRIMARY KEY (subfieldid), - KEY (bibid,tagid,subfieldcode) - ) TYPE=MyISAM; - CREATE TABLE marc_7XX_subfield_table ( - subfieldid bigint(20) unsigned NOT NULL auto_increment, - tagid bigint(20) NOT NULL default '0', - bibid bigint(20) NOT NULL default '0', - subfieldorder tinyint(4) NOT NULL default '0', - subfieldcode char(1) NOT NULL default '', - subfieldvalue varchar(255) default NULL, - valuebloblink bigint(20) default NULL, - PRIMARY KEY (subfieldid), - KEY (bibid,tagid,subfieldcode) - ) TYPE=MyISAM; - CREATE TABLE marc_8XX_subfield_table ( - subfieldid bigint(20) unsigned NOT NULL auto_increment, - tagid bigint(20) NOT NULL default '0', - bibid bigint(20) NOT NULL default '0', - subfieldorder tinyint(4) NOT NULL default '0', - subfieldcode char(1) NOT NULL default '', - subfieldvalue varchar(255) default NULL, - valuebloblink bigint(20) default NULL, - PRIMARY KEY (subfieldid), - KEY (bibid,tagid,subfieldcode) - ) TYPE=MyISAM; - CREATE TABLE marc_9XX_subfield_table ( - subfieldid bigint(20) unsigned NOT NULL auto_increment, - tagid bigint(20) NOT NULL default '0', - bibid bigint(20) NOT NULL default '0', - subfieldorder tinyint(4) NOT NULL default '0', - subfieldcode char(1) NOT NULL default '', - subfieldvalue varchar(255) default NULL, - valuebloblink bigint(20) default NULL, - PRIMARY KEY (subfieldid), - KEY (bibid,tagid,subfieldcode) + KEY (bibid,tagid,tag,subfieldmark), ) TYPE=MyISAM; - # marc_blob_tag containts tag longer than 255 car. # They are linked to a marc_NXX_tag_table record by bloblink CREATE TABLE marc_blob_tag ( @@ -227,6 +33,24 @@ PRIMARY KEY (blobidlink) ) TYPE=MyISAM; + + + +# The next two tables are used for labelling the tags and subfields for +# different implementions of marc USMARC, UNIMARC, CANMARC, UKMARC, etc. + +# marc_tag_structure contains the definition of the marc tags. +# any MARC is supposed to be support-able + CREATE TABLE marc_tag_structure ( + tagfield char(3) NOT NULL default '', + liblibrarian char(255) NOT NULL default '', + libopac char(255) NOT NULL default '', + repeatable tinyint(4) NOT NULL default '0', + mandatory tinyint(4) NOT NULL default '0', + PRIMARY KEY (tagfield) + ) TYPE=MyISAM; + + # marc_subfield_structure contains the definition of the marc # subfields. Any MARC is supposed to be support-able CREATE TABLE marc_subfield_structure ( @@ -240,16 +64,8 @@ PRIMARY KEY (tagfield,tagsubfield) ) TYPE=MyISAM; -# marc_tag_structure contains the definition of the marc tags. -# any MARC is supposed to be support-able - CREATE TABLE marc_tag_structure ( - tagfield char(3) NOT NULL default '', - liblibrarian char(255) NOT NULL default '', - libopac char(255) NOT NULL default '', - repeatable tinyint(4) NOT NULL default '0', - mandatory tinyint(4) NOT NULL default '0', - PRIMARY KEY (tagfield) - ) TYPE=MyISAM; + +# This table is the table used for searching the marc records # marc_tag_word contains 1 record for each word in each subfield in each tag in each biblio CREATE TABLE marc_tag_word ( @@ -263,15 +79,3 @@ KEY sndx_word (sndx_word) ) TYPE=MyISAM; - - - - - - - - - - - - -- 2.39.2