1

For various reasons I have decided to use surrogate keys (in the form of a seed identity). My question comes from enforcing data uniqueness . For example, take the scenario I am creating a Database to store all the books in a library.

An isbn is a unique identifier assigned to all books from some magic source in the book publishing world. If the book is printed it has 1 isbn if it is electronic it has another ISBN.

We cannot enforce uniqueness on the ISBN without putting a uniqueIndex covering the printedISBN and ElectronicISBN column. The specific question I have is , We have this type of scenario and we need to enforce uniqueness of data but we are using a surrogate primary key, the only way we have to enforce data uniqueness is by putting a unique index on the columns we would like to enforce. This seems counter intuitive because if we followed the surrogate key methodology throughout our design every table would have a surrogate primary key and a unique index?? This seems bad and I feel my design skill is not strong enough to find the “right answer”. What is that answer in these types of scenarios?

BookID INT IDENTITY (1,1) not null,
Title varchar(25) not null,
Author varchar(25) not null,
PrintISBN char(13) not null,
ElectronicISBN char(13) not null
gh9
  • 10,169
  • 10
  • 63
  • 96
  • 1
    One possible answer is you normalize off the ISBN details to another table, BookID, ISBN, ISBNType, OtherMeta field - and then its trivial to place a unique constraint on ISBN. – Andrew Nov 02 '11 at 14:52
  • makes sense. Have the primary key be the isbn have a column be isbn type, then Have another table with typeID, ISBN type. This would allow me to put any type of guid in there as long as i associate it with a following type, so if they come out with "isbnMark" edition i can easliy adapt to it. – gh9 Nov 02 '11 at 14:59

4 Answers4

3

(Converted form comments)

The ISBN should be normalized out to a seperate table. For a given book you could be dealing with multiple ISBN's, whether it is hardback vs softback vs braille for physical medium, vs audio book and ebook electronic editions. We havn't even considered whether translated editions of the book get's a seperate number or not.

There are multiple ways a book for a given author can be released, and I don't think you should limit the ISBN's a book can have to 2 - 1 printed, 1 electronic.

Uniqueness can then be enforced without a problem.

Andrew
  • 26,629
  • 5
  • 63
  • 86
  • Great comment and point. I was addressing the more generic question "can you enforce uniqueness on a second column in a table that has the surrogate key" and you definitely can (and you should identify and select the natural key) but this answer gets to the heart of the specific situation for the OP and leads towards thinking about a better DB design. – Mike Walsh Nov 02 '11 at 15:16
3

If the book is printed it has 1 isbn if it is electronic it has another ISBN.

I suspect this is merely one of many classifications e.g. hardback, paperback, audiobook, Kindle, CD, download. Also consider multiple publishers, languages, locales, etc. One of the great things about using ISBN is that you don't have to wonder about any of this: you just have to know what the item's ISBN's number then compare it with another item's ISBN number to find out whether they are considered to be the same.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
2

There is no problem having unique indexes and surrogate keys that are unique on the same table. In fact it is a requirement when using surrogate keys to still declare "Natural Key" indexes and make them unique (otherwise it is simply an artificial key i.e. it is not a surrogate for anything).

I would certainly create unique indexes on the ISBNs to enforce their uniqueness and also to let the query optimizer know the data in the columns is unique. The more information we hand to the query optimizer the better performing the resulting plan will be.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
Mike Walsh
  • 869
  • 7
  • 10
  • Wont this methodology lend to large database files by having extra indexs on columns? – gh9 Nov 02 '11 at 15:01
  • Will you ever be searching by ISBN? If so, that isn't a bad index to have. 13bytes x however many rows. – Mike Walsh Nov 02 '11 at 15:03
  • 1
    But your problem may come in in having both ISBNs. What do you do when a book has no electronic copy? What do you when it only has an electronic copy? One solution could be to create a filtered unique index that allows nulls - http://stackoverflow.com/questions/767657/how-do-i-create-unique-constraint-that-also-allows-nulls-in-sql-server – Mike Walsh Nov 02 '11 at 15:04
  • 2
    Surely it is a prerequisite of a surrogate to have a second key? Otherwise it is simply an artificial key i.e. it is not a surrogate for anything. – onedaywhen Nov 02 '11 at 15:06
  • @onedaywhen- Is it a prereq for a surrogate key to have a second key? I do not know. It would make sense for it to, to enforce data integrity. I just dont want to design a thing use a bunch of secondary keys when there is a better way. I decided to go with andrews comment as the solution to my issue. But it is good to know that having secondary keys with surrogate keys is pretty much a standard. Thank you – gh9 Nov 02 '11 at 15:11
  • @gh9, if you value data integrity then having a business key in addition to any surrogate is essential. Otherwise users would have no way to identify facts in the database or verify their accuracy in the real world. – nvogel Nov 02 '11 at 16:14
1

Yes, every table ought to have a natural key (AKA "business key") whether or not it also has a surrogate. Use uniqueness constraints to enforce keys. The proper use of indexes in addition to those constraints is a different and essentially unrelated question.

Whether ISBN is the right business key for your table is impossible for me to say without analysis of the business requirements. Suffice to say that there should be some way to identify the entity you are modelling within the business domain.

nvogel
  • 24,981
  • 1
  • 44
  • 82