 |
 |
On 5/22/06, Houghton,Andrew <[log in to unmask]> wrote: > > 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.? Wait. A few seconds? You're kidding, right? An indexed look up of the group id for the original ISBN, and then 1 subsequent look up by that group id (should also be indexed) should be on the order of 1 millisecond /total/. Equality index scans are fast in every database worth using. > > 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. > Just so we're clear, we are talking about, at most, .5M rows that are perhaps 50 bytes wide, with internal data. That translates to ~25 megabytes for the table, and the indexes would be /maybe/ 5 megs each. > 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. > Can you not search by ISBN and type in the main catalog? (I'm not a Horizon user, so I have no idea.) > 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. > -- Mike Rylander [log in to unmask] GPLS -- PINES Development Database Developer http://open-ils.org