[Koha-devel] MARC DB schema

[Tonnesen Steve]

I stole the MARC DB schema that I am using with Koha from somebody.  I'd
love to attribute, but I can't find it right now.  It'll be attributed
back in the koha-devel archives somewhere.

I have done some work towards integrating this schema with the existing
Koha database.  My goal was to create an API that would abstract out the
underlying database, so that changes made to a record could be made to
either the old Koha style database or the new MARC database.

My testing has shown the schema given below to be pretty poor at lookups.
Some kind of separately maintained indexes would almost certainly be
necessary.


# Resource Table holds information about a particular resource

Resource_Table
  Resource_ID   | int(10) unsigned auto_increment primary key
  Date_Added    | timestamp(14)    
  Date_Modified | timestamp(14)    
  Record_ID     | int(10) unsigned 


# Bib_Table holds a record for each Tag in the MARC record.  Links
# back to Resource_Table with the Record_ID field

Bib_Table
  Bib_ID        | int(10) unsigned 
  Record_ID     | int(10) unsigned 
  Date_Added    | timestamp(14)    
  Date_Modified | timestamp(14)    
  Control_ID    | int(10) unsigned 
  Tag_0XX_ID    | int(10) unsigned 
  Tag_1XX_ID    | int(10) unsigned 
  Tag_2XX_ID    | int(10) unsigned 
  Tag_3XX_ID    | int(10) unsigned 
  Tag_4XX_ID    | int(10) unsigned 
  Tag_5XX_ID    | int(10) unsigned 
  Tag_6XX_ID    | int(10) unsigned 
  Tag_7XX_ID    | int(10) unsigned 
  Tag_8XX_ID    | int(10) unsigned 
  Tag_9XX_ID    | int(10) unsigned 



# And for each of #XX, there are two tables, 6XX_Tag_Table and
# 6XX_Subfield_Table.
#
# 6XX_Tag_Table holds a record for each 600s Tag in the MARC record.
#   Tag_ID links back to the Tag_6XX_ID field

6XX_Tag_Table
  Tag_Key      | int(10) unsigned auto_increment primary key
  Tag_ID       | int(10) unsigned
  Indicator1   | char(1)           
  Indicator2   | char(1)           
  Tag          | char(3)           
  Subfield_ID  | int(10) unsigned  
  Authority_ID | int(10) unsigned  
  Link_Flag    | enum('Y','N','B') 
  Storage_ID   | int(10) unsigned  


# 6XX_Subfield_Table holds a record for each subfield in each tag.
#   Subfield_ID links back to Subfield_ID in the 6XX_Tag_Table

6XX_Subfield_Table
  Subfield_Key   | int(10) unsigned auto_increment primary key
  Subfield_ID    | int(10) unsigned
  Subfield_Mark  | char(1)          
  Subfield_Value | char(255)        
  Storage_ID     | int(10) unsigned 



#  The storage table is used for storing data that will not fit in a
# standard 255 character database field.  The Storage_ID field is used to
# link to 6XX_Subfield_Table's Storage_ID, for example.

Storage_Table
  Storage_Key  | int(10) unsigned        
  Storage_ID   | int(10) unsigned        
  Blob_ID      | int(10) unsigned        
  Text_ID      | int(10) unsigned        
  Med_Blob_ID  | int(10) unsigned        
  Med_Text_ID  | int(10) unsigned        
  Long_Blob_ID | int(10) unsigned        
  Long_Text_ID | int(10) unsigned        
  URI          | char(255)               
  Storage_Type | enum('B','MB','LB','U') 


The schema I stole had a slew of other tables dealing with Authorities and
Holdings that I haven't documented here.


Steve.