Why do we add an Id column to our tables?

robbaralla · November 18th, 2009, 09:54 AM

Hello Imar,

I wanted to ask you a more general question, maybe this topic is outside the scope of the book and I should just accept it as good practice to give each item in a table its own Id.

So my question is; why do you add an Id column to the tables?

At first sight it seems that we are just adding an extra column to our data that could be considered redundant, because most of the time already a review name for example is unique. And then again if it's not unique the combination of that review name with the genre name makes it unique.

In the discussion of primary keys on p373, you say that the primary key can span multiple columns, where the columns together form a unique Id.
I haven't been able to find out how to do that, so how is that accomplished?

Best regards, Robin

Imar · November 18th, 2009, 10:37 AM

Quote:

because most of the time already a review name for example is unique. And then again if it's not unique the combination of that review name with the genre name makes it unique.

Really? What if you and I both write a review about Placebo's Battle for the Sun album in the Rock genre and call it:

Placebo's new album: Battle for the Sun

Then the system would break, as you would have a duplicate key error: e.g. two records would try to share the same unique ID. This may be an unlikely scenario, especially in very low traffic sites, but it's a real problem in bigger sites as you just cannot trust on the uniques of the ID.

Another reason is the ease at which you can access records:

ShowDetails.aspx?Id=123

is a lot easier to manage than:

ShowDetails.aspx?Artist=Placebo&Album=Battle for the Sun

Also, internally, this is easier (and quicker) to implement and execute.

So, I typically add an Id key (a surrogate key) to a table when it doesn't have a natural key. A natural key is something that is already a key in the real word. For example, an EmployeeNumber in a wages system. No need to add an Id as you already have an EmplyeeNumber which is supposed to be unique. Same goes for books: the ISBN is supposed to be unique so you can store books by their external ISBN (although I think in this case I would still opt for a surrogate key and use the ISBN simply as an external reference).

Does this clarify things?

With regards to the keys: you can select multiple columns in the table designer and click the Primary Key button on the Table Designer toolbar.

Hope this helps,

Imar

robbaralla · November 18th, 2009, 11:41 AM

Thanks Imar,

I am working on a site where it is sofar only for the site managers possible to add records, so it's unlikely that there will be a conflict.
But I get your point, it might be the easiest and safest solution to add a surrogate key and again also in the querystring it's easier.

I tried to set the primary key on 2 columns, and that works, for now however I'll stick to adding an Id key.

Cheers, Robin

Old Pedant · November 18th, 2009, 05:47 PM

I think that maybe the best argument for an autoincrement id field is the ability to use it as a foreign key in related tables.

Yes, you *can* use "Placebo's new album: Battle for the Sun :: Rock" as the primary key (ignoring Imar's of course valid argument), but then how do you reference that in related tables??? The only way would be to have TITLE and GENRE fields in the other table and copy those two values into them for each related record. It works. It is indeed correct Normalization. But ugh...it makes the DB bigger and slower.

There are many DB experts who advocate against the use of autoincrement values as PKs. For a variety of reasonable reasons. The best reason, to me, is that people end up depending on the sequential nature and make silly assumptions about ordering that then later turn out not to be true (e.g., after many deletes and updates). They would have you use a GUID or similar for the ID. Hard to argue against that, except that a GUID is 16 bytes and unless your tables are huge you can get away with a standard 4 byte INT using autoincrement. Just don't rely on the sequential nature, and I think you are good to go.

robbaralla · November 19th, 2009, 04:39 AM

Hi Old Pedant,

I appreciate your reply, the ability to easier use the ID as a FK is the last push I needed to be convinced to stick to using an auto incremented Id column, even if this introduces extra data in the table.

Best regards, Robin

Imar · November 19th, 2009, 05:09 AM

Although you're already convinced, here's one more reason. Let's say you initially post the review like this:

Placbo's new album: Battle for the Sun

(Note the spelling mistake in Placebo's name).

Now, some visitor comes along and bookmarks the page like this:

ShowDetails.aspx?Review=Placbo's%20new%20album:%20 Battle%20for%20the%20Sun&genre=Rock

Next, you decide to fix the spelling mistake. Besides changing the title, you also implicitly update the record's key which also means you need to update related records that were using the old title and genre. But worse: the previous bookmark is now broken as it was content related, not key related. Using a surrogate ID would have made all this a lot easier.

For Search Engine Optimization, having review titles and what not in the page name is still a good idea though,

Imar

robbaralla · December 5th, 2009, 06:18 AM

Hi Imar,

I wanted to come back to this topic, because even though all your arguments and also the argumets of Old Pedant are valid, there still is a problem.

Now take the NewAlbum.aspx page, imagine that a user came up with the brilliant idea to make a new album called "Latitude Festival 2007", even though this album name already exists, there is nothing that stops her from doing that because in the dataBase the album simply gets a new Id. We haven't told the dataBase to stop an insert when the album name is already existant....

So because the name is not used as a key, now in the ddl on the Albums/default.aspx 2 albums with the same name show up.

So how do we solve this problem?

Best regards, Robin

Imar · December 5th, 2009, 10:51 AM

Hi Robin,

It depends on how bad you think this is. It's quite common to have multiple records with the same name (think FirstName / LastName conbinations for example).

I can see a few "fixes" to the "problem".

1. Display the user name and the date and time the review was posted:

Latitude Festival 2007 posted by Imar on 2007/10/10 10:23:17 PM

This makes it less likely you have conflicting items.

2.If the item must be unique (which could be the case, depending on your own business rules) you can check if the item exists in the database and cancel the insert (and updates) if they do already exist.

3. Make the column Title (or any other column) unique in the database by creating a check constraint. You can do this in the Table Designer.

Personally, I'd go for option 1 in this scenario, but 2 and 3 are common in other scenarios as well.

Cheers,

Imar

robbaralla · December 5th, 2009, 04:48 PM

Thanks Imar,

That helps, the items haev to be unique, because they are place names with service attached to them.

Best regards, Robin