 |
 |
> From: Code for Libraries [mailto:[log in to unmask]] On > Behalf Of Ross Singer > Sent: 22 May, 2006 11:58 > To: [log in to unmask] > Subject: Re: [CODE4LIB] Musings on using xISBN in our Horizon catalog > > Er, how many records are we talking about? > > I don't really see this table getting too unmanageable. He's > not frbrizing Worldcat this way, after all. > Lets say that the catalog we are talking about has 30,000 items. Also, lets say that xISBN, on average, returns 4 related ISBN's per the given ISBN in the catalog for each of those 30,000 items. Using the ISBN to ISBN relationship table, described in my previous message, we will have 16 rows * 30,000 items for a total of 480,000 rows in the database. While that may not be too unmanageable, depending upon hardware and SQL database being used, if we look at the FRBR to ISBN relationship table, described in my previous message, we will have 4 rows * 30,000 items for a total of 120,000 rows. That is *roughly* 4 times less data that the SQL database has to index and search through per query. Given the size of both datasets, we are still talking about a few seconds per query with most currently available SQL databases. But what happens when the size of the catalog doubles, triples, quadruples, etc.? The tradeoff is that the ISBN to ISBN relationship table uses more storage space which translates to longer database reindexing, backup, etc. times from an operational perspective. It also means that during updates, you have 4 times the number of records to update which will impact performance. Also, if we try to scale the ISBN to ISBN relationship table to a catalog with significantly more items, then it will become unmanageable. When reading Ben's description of the problem he states: "we want to be able to display links to other versions of the same work (audiobooks, large print, possibly translations)." From a database design, you probably want to track "audiobook", "large print", etc. with the item. Using the ISBN to ISBN relationship table design, because the data is unnormalized, we will continue to store duplicate information in the database as we extend the database design: Column 1 Column 2 Column 3 -------- -------- -------- ISBN-1 ISBN-1 book ISBN-1 ISBN-2 audiobook ISBN-1 ISBN-3 large print ISBN-1 ISBN-4 video ISBN-2 ISBN-1 book ISBN-2 ISBN-2 audiobook ISBN-2 ISBN-3 large print ISBN-2 ISBN-4 video ISBN-3 ISBN-1 book ISBN-3 ISBN-2 audiobook ISBN-3 ISBN-3 large print ISBN-3 ISBN-4 video ISBN-4 ISBN-1 book ISBN-4 ISBN-2 audiobook ISBN-4 ISBN-3 large print ISBN-4 ISBN-4 video we could mitigate this duplicate information in the database design by creating a "format" table and use the Foreign Key. However, we still have 12 duplicate foreign keys because the original data was unnormalized. When looking at the FRBR to ISBN relationship table, you have normalized out the "work" and "item" levels and can attach information at either the "work" or "item" levels *without* duplicating information in the database. Column 1 Column 2 Column 3 -------- -------- -------- GROUP-1 ISBN-1 book GROUP-1 ISBN-2 audiobook GROUP-1 ISBN-3 large print GROUP-1 ISBN-4 video Again we could, and probably should, create a "format" table and use a foreign key for Column 3. You can see that as you start to extend the database designs, the ISBN to ISBN relationship table will become unmanageable from a design standpoint and will also cause additional duplicate information to be stored in the database, which will increase storage requirements, etc. A good database design will go a long way... Andy.