0

Possible Duplicate:
Is there a REAL performance difference between INT and VARCHAR primary keys?

It could be a silly question but is it better to use an ID instead name when we create a table in SQL?

I have created tables for company and client and I have just connected 2 tables using company name. However I have seen from other threads that lots of people uses company_ID as a primary key and connect it with ID from other tables.

is there any good reason that they are using ID instead name of the company?

Thanks

Community
  • 1
  • 1
warang
  • 43
  • 2
  • 10
  • ID will be and shall be unique whereas a company name might not be – dchhetri Nov 20 '12 at 01:50
  • 2
    consider what happens when a company renames itself... now you're having to change (or at least propagate) the new name to every single table where the old name occurs. "oops, missed one...". dragging around that int detaches the name from anything in your db, letting the ceo waste their time with renaming while you can get on actually accomplishing something useful. – Marc B Nov 20 '12 at 01:52
  • @pst: consider that mysql has a cascade depth limit of 15. not enough for a few schemas I've unfortunately had to deal with. – Marc B Nov 20 '12 at 01:58
  • You should also be concerned with the impact this will have on the SIZE of your database. http://www.sqlskills.com/blogs/paul/post/Survey-results-How-cluster-key-size-can-lead-to-GBs-of-wasted-space.aspx – MikeSmithDev Nov 20 '12 at 02:46
  • click the link above (*Is there a real performance....*) – John Woo Nov 20 '12 at 06:10

1 Answers1

0

There are several considerations in choosing to use "natural" or "surrogate" keys. Examine each carefully. This article discusses some of the pros and cons of each.

Generated ID's may often be a good choice, but the best answer may depend on what data you are storing, and how that database will be used. For example, in the case of a book database, title would not make a good primary key, since two authors may write different books but use the same title. ISBN might make a good natural key. The argument is often made that a natural key is inconvenient to change. How often does the ISBN of a book change? On the other hand, company names do occasionally change.

It is also common to use company numbers, but these are commonly already assigned by the accounting department. Why would you prefer to store a generated CompanyID, and always have to look up what CompanyNbr that the ID refers to?

WarrenT
  • 4,502
  • 19
  • 27