[XML4Lib] Database schema for MARCXML?

[Mike Rylander]

On 8/2/06, jkglenn at umich.edu <jkglenn at umich.edu> wrote:
> Hello,
>
> Hopefully the list is willing to entertain a naive question from a MARC
> and XML newbie. I'm trying to figure out the best way to store MARCXML
> records in a relational database, and am wondering if there are any
> standard database schemas out there for doing this. In particular I
> would like to avoid having to write database queries to replicate the
> functionality of CCL commands that search a particular set of MARC
> fields. I've found some limited info on the Library of Congress
> website, but nothing very helpful. If someone could point me in the
> right direction I would be most grateful.

Unfortunately, MARC doesn't translate to a relational model very well
... not in a normalized, scalable way, at least.

There are basically 2 routes you can take:

Flat, denormalized table of field-subfield-data, something like this

CREATE TABLE marcdata (
  recid       int,
  tag          char(3),
  ind1        char(1),
  ind2        char(1),
  subfield  char(1),
  data        varchar(1024)
);

Or you can go to a normalized, multi-table setup, along these lines

CREATE TABLE marcdoc (
  id           int,
  source  text
);

CREATE TABLE marctag (
  id          int,
  record  int,
  tag       char(3)
);

CREATE TABLE controlfield (
  tag       int,
  data     text,
),

CREATE TABLE datafield_indicators (
  tag         int,
  ind1       char(1),
  ind2       char(2)
);

CREATE TABLE datafield_subfield
  tag          int
  subfield  char(1),
  value      text
);


Hope that helps! :)

>
> Thanks,
> Jacob Glenn
> _______________________________________________
> XML4Lib mailing list
> XML4Lib at webjunction.org
> http://lists.webjunction.org/mailman/listinfo/xml4lib
>


-- 
Mike Rylander
mrylander at gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org