CODEWITHSUNDEEP
sql
gh9
gh9

Reputation: 10703

SQl Design issue. Unique Index/Constraint vs Surrogate Keys

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

Upvotes: 1

Views: 491

Answers (4)

nvogel
nvogel

Reputation: 25526

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.

Upvotes: 1

Mike Walsh
Mike Walsh

Reputation: 899

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.

Upvotes: 2

onedaywhen
onedaywhen

Reputation: 57023

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.

Upvotes: 3

Andrew
Andrew

Reputation: 27294

(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.

Upvotes: 3

Related Questions