> From: Code for Libraries [mailto:[log in to unmask]] On
> Behalf Of Ben Ostrowsky
> Sent: 22 May, 2006 09:47
> To: [log in to unmask]
> Subject: [CODE4LIB] Musings on using xISBN in our Horizon catalog
>
> [Description of Problem, deleted...]
>
> My guess is that we should have a row for each ISBN in the
> system, along with a column that links that ISBN to some
> common identifier that will symbolically mean e.g. "SQL for
> Dummies, any version".  We could then ask "What is the
> identifier for the first ISBN of the item being displayed?"
> and then do a second query that asks "What other ISBNs in our
> catalog have that identifier?"
>
> I'm most concerned with choosing a good identifier.  If
> there's a way to uniquely identify a FRBR work, then surely
> that identifier would be the best key to use.  If not,
> perhaps someone could suggest something?
>

Being somewhat partial, you might use OCLC's FRBR Work-Set
algorithm [1].

If I were setting up the SQL database, I would probably do the
following:

FRBR table
----------
PK_FRBR
txtFRBRWorkSetKey
*Any other work level related information*

where PK_FRBR is an integer primary key


ISBN table
----------
FK_FRBR
txtISBN
*Any other item level related information*

where FK_FRBR is the foreign key into the FRBR table.


As a personal preference I never use actual data, e.g., the
txtFRBRWorkSetKey directly as the index.  Hence the separate
PK_FRBR and related FK_FRBR indexes.  One could argue, to use
txtFRBRWorkSetKey as the index, but an integer primary key
will use 32-bits or 4 bytes.  The actual FRBR Work Set key
will typically be much larger.  Have it specified in both
tables would substantially increase the size of the database,
even with the SQL database compressing the key in both tables.


Andy.

[1] <http://www.oclc.org/research/software/frbr/default.htm>