Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3
This is the forum to discuss the Wrox book Beginning ASP.NET 3.5: In C# and VB by Imar Spaanjaars; ISBN: 9780470187593
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 18th, 2009, 08:54 AM
Authorized User
 
Join Date: Apr 2009
Location: Sardinia - Italy
Posts: 48
Thanks: 16
Thanked 0 Times in 0 Posts
Default Why do we add an Id column to our tables?

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
Reply With Quote
  #2 (permalink)  
Old November 18th, 2009, 09:37 AM
Imar's Avatar
Wrox Author
Points: 71,164, Level: 100
Points: 71,164, Level: 100 Points: 71,164, Level: 100 Points: 71,164, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,948
Thanks: 79
Thanked 1,555 Times in 1,532 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
The Following User Says Thank You to Imar For This Useful Post:
robbaralla (November 18th, 2009)
  #3 (permalink)  
Old November 18th, 2009, 10:41 AM
Authorized User
 
Join Date: Apr 2009
Location: Sardinia - Italy
Posts: 48
Thanks: 16
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old November 18th, 2009, 04:47 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
Reply With Quote
The Following User Says Thank You to Old Pedant For This Useful Post:
robbaralla (November 19th, 2009)
  #5 (permalink)  
Old November 19th, 2009, 03:39 AM
Authorized User
 
Join Date: Apr 2009
Location: Sardinia - Italy
Posts: 48
Thanks: 16
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old November 19th, 2009, 04:09 AM
Imar's Avatar
Wrox Author
Points: 71,164, Level: 100
Points: 71,164, Level: 100 Points: 71,164, Level: 100 Points: 71,164, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,948
Thanks: 79
Thanked 1,555 Times in 1,532 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
The Following User Says Thank You to Imar For This Useful Post:
beszpilman (November 23rd, 2009)
  #7 (permalink)  
Old December 5th, 2009, 05:18 AM
Authorized User
 
Join Date: Apr 2009
Location: Sardinia - Italy
Posts: 48
Thanks: 16
Thanked 0 Times in 0 Posts
Default Why do we add an Id column to our tables CONTINUED..

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
Reply With Quote
  #8 (permalink)  
Old December 5th, 2009, 09:51 AM
Imar's Avatar
Wrox Author
Points: 71,164, Level: 100
Points: 71,164, Level: 100 Points: 71,164, Level: 100 Points: 71,164, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,948
Thanks: 79
Thanked 1,555 Times in 1,532 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
  #9 (permalink)  
Old December 5th, 2009, 03:48 PM
Authorized User
 
Join Date: Apr 2009
Location: Sardinia - Italy
Posts: 48
Thanks: 16
Thanked 0 Times in 0 Posts
Default

Thanks Imar,

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

Best regards, Robin
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
identity column id find!! keyvanjan Classic ASP Basics 7 January 20th, 2007 12:12 PM
How to insert 2 tables with same auto increment ID ibrhaim ASP.NET 1.0 and 1.1 Basics 1 December 7th, 2006 04:00 PM
ID column used to redirect page rdove84 ASP.NET 1.0 and 1.1 Basics 11 November 14th, 2006 04:10 PM
Hide Tables by Id itHighway Javascript How-To 4 April 23rd, 2005 12:28 AM
write to 2 tables using same id. mikeuk PHP Databases 5 July 22nd, 2004 09:16 AM



All times are GMT -4. The time now is 11:58 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.