Re: How to deal with identical fields in db

tedd wrote: > At 3:14 AM -0700 5/6/09, Michael A. Peters wrote: >> Peter Ford wrote: >>> >>> tedd wrote: (and I added in some extra bits...) >>>> You need to normalize. >>>> >>>> Authors should have an unique id in an authors table. The authors table >>>> has all the specific information about authors, but not the books they >>>> have written. >>>> >>>> Books should have an unique id in a books table. The books table has >>>> all >>>> the specific information about books, but not the contributing authors. >>>> >>> >>> Like the ISBN, for example - that should be unique enough for anyone... >>> I suppose if you deal in antique books, there might not be an ISBN. >> >> Unfortunately sometimes an otherwise identical but different printing >> of the same book has different ISBN numbers. Sometimes the difference >> is hardback vs softcover, special edition, or just a reprint. >> >> The L.O.C. catalog number may be better, AFAIK there is typically only >> one LOC number per edition of a book. It is a good idea to record both >> (if both exist) and use an internally assigned substitute number when >> one, the other, or both don't exist (small run self published works >> often don't have a LOC number for example, if the author didn't want >> to pay for it). > > > But for a database, a book identifier would probably be best (differing > opinions on this) if it was simply an auto_increment unsigned integer > primary key. A key that is generated upon entry of a book record. > > Certainly one can argue that using a different unique key might provide > more information and make the table require one less field, but if one > uses a primary key, then the field can be searched faster than using a > ISBN or L.O.C., which may be duplicated, amended, or not even present. > My thinking on this is a unique identifier for the book should not be > tied to any attribute of the book, which may change, but rather > something completely detached and artificial. > > Cheers, > > tedd > tedd, That is, in fairness, probably what I'd do too: I might have the ISBN or LOC number as a detail field in the book record, and have it available for look-ups, but the primary key would just be a sequence number generated automatically. Same with authors, just a sequence number for the key. (I am not a number, I am a free man...) These things do not need to be visible to the user. Just an implementation detail, nothing to see here... :) Cheers Pete -- Peter Ford phone: 01580 893333 Developer fax: 01580 893399 Justcroft International Ltd., Staplehurst, Kent