I would like to update all the Koha developers on two issues that have come up as NPL has been migrating to Koha. Both relate to the difference between the way Koha stores bibliographic information and the way MARC records (USMARC) structures the same information. First a reminder of what those differences are. Koha subdivides bibliographic information into three tables (basically): biblio, biblioitems, and items. Biblio holds the basic information about the work -- title and author, copyright date, that sort of thing. Biblioitems holds information about a particular manifestation of the work -- item type, classification, actual publication year (which may be different from the copyright year), etc. And items stores information about individual copies of the particular manifestation of the work -- price, barcode number, date acquired, etc. MARC, on the other hand, currently subdivides bibliographic data into two area: "Bibliographic," which holds the information that Koha would put in biblio and (some) biblioitems, and "Holdings," which has the information that Koha would put in items and (some) biblioitems. It's the process of fitting two-part information into a three-part database that leads to complications. 1. The process of importing MARC records results in one and only one row in biblioitems for each row in biblio. That's because MARC makes an individual record for each manifestation of a work, so when you import a MARC record you are really importing each record into biblioitems, with a related row in biblio to hold the information that cannot be mapped to biblioitems (author, title, etc.). That, of course, is exactly backwards from the way Koha is designed to work. The import works OK, but when you do a search, you get lots of duplicate titles listed, because each printing or video or audio recording of a work has its own row in biblio. That makes it very hard to decide which title listing you want to look at more closely. Which "Gone with the wind" is the audio recording? MARC handles this problem by providing tag 245h for the "Medium" of the work, surrounded by square brackets. (The title itself is in 245a.) Many MARC-based library systems display this tag when showing the results of a search, so you know that "Gone with the wind [audio recording]" is different from "Gone with the wind [videorecording(DVD)]" or "Gone with the wind." There are two solutions I can think of for this problem, neither of them very satisfactory. One is to add a column to biblio to hold "medium" for the 245h tag. That, of course, violates the whole philosophy of what the biblio table is supposed to store. The other is to actually modify the title that is stored in biblio. That's the workaround solution we are using at NPL. We periodically run a crude but efficient script that handles the job: my $sth_getformat = $dbh->prepare("SELECT bibid,subfieldvalue FROM marc_subfield_table WHERE tag = '245' and subfieldcode = 'h'"); my $sth_gettitle = $dbh->prepare("SELECT title FROM biblio WHERE biblionumber = ?"); my $sth_put = $dbh->prepare("UPDATE biblio SET title = ? WHERE biblionumber = ?"); $sth_getformat->execute(); my $row; while ($row = $sth_getformat->fetchrow_arrayref) { my $bibid = $row->[0]; my $subfieldvalue = $row->[1]; $sth_gettitle->execute($bibid); my $titleref = $sth_gettitle->fetchrow_arrayref; my $title = $titleref->[0]; $sth_gettitle->finish; $subfieldvalue =~ /.+]/; my $newtitle = "$title $&"; $sth_put->execute($newtitle,$bibid); } Could something similar be included as part of the MARC import process? It's not elegant, but it does solve the problem. Or better yet, can anyone think of a way to combine duplicate biblio rows into one biblio row? (Seems like this would really screw up the relationships between tables.) 2. While Koha stores the copyright date in biblio and the publication year in biblioitems, MARC puts both in one tag (260c), which of course can only be mapped to one Koha table.column. So currently the library importing their MARC records into Koha has to decide which Koha table.column to fill, and then the Koha Biblio.pm strips out the first date found in the 260c tag and puts it there. This is not good, because: a) either the screens which display biblio.copyrightdate or the screens which display biblioitems.publicationyear are going to have nothing to display; b) we're losing information in the import which could easily be retrieved; and c) it leads to inaccurate information, because (in the US) if 260c has two dates, the first is always the publication year and the second is the copyright date, and the current Koha solution could end up putting the publication year in the copyright date column. Again, we periodically run a (crude) script at NPL to load both table.columns: my $sth_get = $dbh->prepare("SELECT bibid,subfieldvalue FROM marc_subfield_table WHERE tag = '260' and subfieldcode = 'c'"); my $sth_cprdate = $dbh->prepare("UPDATE biblio SET copyrightdate = ? WHERE biblionumber = ?"); my $sth_pubdate = $dbh->prepare("UPDATE biblioitems SET publicationyear = ? WHERE biblionumber = ?"); $sth_get->execute(); my $row; while ($row = $sth_get->fetchrow_arrayref) { my $bibid = $row->[0]; my $subfieldvalue = $row->[1]; if (length $subfieldvalue > 8) { # if it is this long (even with extra # letters and punctuation), it must be # publication date, copyright date $subfieldvalue =~ /(\d{4}).+?(\d{4})/; $pubdate = $1; $cprdate = $2; } elsif ($subfieldvalue =~ /(\d{4})/) { # only one date $pubdate = $1; $cprdate = $1; } else { # no dates $pubdate = ''; $cprdate = ''; } $sth_cprdate->execute($cprdate,$bibid); $sth_pubdate->execute($pubdate,$bibid); } Again, could this somehow be worked into the MARC import process? Stephen Hedges Director, Nelsonville Public Library 95 W. Washington St., Nels.,OH 45764 (740) 753-2118 fax (740) 753-3543