On Sat, 2005-04-30 at 13:19 +1000, Alan McDonald wrote:

>
> > Disk space is cheap - $1 per gigabyte. The surrogate integer key
> > requires an additional field and does not help in the business at hand.
>
> but seeks over and ever enlarging number of DB pages is more time consuming
> compared with scans over fewer index pages no?
>
> Alan

Yes ... but an extra surrogate key on the relation table, over and above
the keyword-<<primarykey>> structure, only adds to the page count - it
is not useful for seeking.

Unless I am mis-reading your intent, and you are suggesting using an
integer instead of an ISBN as the <<primary key>> on the book table. I
won't argue about that - ISBN versus a surrogate integer is a triviality
once the conceptual foundation is laid.

My only intent is to suggest the use of the indexed relation table to
some small primary key structure, without specifying the key structure.
He can figure out what best suits his purpose as a primary key. Since
books already have a standardized unique identifier (ISBN, which is a
numeric field with a lot of digits). He will be in a better position to
decid that than I would be.

In empirical testing with Firebird, integer versus GUID indexing was 3
I/O versus 4 I/O on the index at 4,000,000 records, with no special
attention paid to header compression. Since the uniqueness in a GUID on
a given physical machine is in the first digits, this resulted in
essentially no header compression.

ISBN is shorter than a GUID expressed as a string. For little databases
with only a few million rows, the difference should not be noticable,
provided the data model and the surrounding processes are sound.