Problem solve Get help with specific problems with your technologies, process and projects.

More on ISBN as a primary key

I would like to follow up on the question regarding primary keys. The example of the ISBN of a book prompts me to ask about how to enable primary keys in a worldwide library environment. If ISBN were actually unique for each book, there would be no problem. But in fact, if we think of a book as its content, there will be different ISBNs for the same book: one for the hardback edition, another for the paperback, a third for the book in a set with other books, a fourth for the edition licensed for publication in the U.K. and so forth. Complicating the problem is the fact that some book ISBNs are unknown or are incorrectly transcribed by libraries. Is there some way to have a universal and persistent ID any one could use anywhere? Perhaps by sending various data elements from a book to a server that would return a valid ID? Thanks for any help with this problem.

(Note: see Questions about primary keys 05 April 2002)

Assuming that a "more unique" code than the ISBN is even possible, it follows that there would be a one-to-many relationship from each such code to the ISBN numbers of the "same" book. Your examples make sense, though, at least to me, but I hasten to point out that I don't know book publishing, or the conditions under which a "new" ISBN is issued to the "same" book.

worldwide code     ISBN       description
  00000101      1230001234    hardback edition 
  00000101      1230005678    paperback edition 
  00000101      1230000937    UK version

From a database design perspective, what would you use as the primary key? In my opinion, the ISBN would still be the PK. The "worldwide code" would be a foreign key, although you might not have a separate table for it. In any case, you can see that to implement this many-to-one relationship (as seen from the perspective of the ISBN) all you need to do is add the new worldwide code column to the existing table that uses ISBN as its primary key.

By the way, have a look at The Digital World and the Ongoing Development of ISBN, part of the isbn.org web site, which I discovered when researching this question. It says that the ISBN will be expanding from 10 digits to 13 in 2005. Not as monumental a change as Y2K, but you may want to start making plans...

Dig Deeper on Oracle DBA jobs, training and certification