Identity Field Controversy

[koga101]

Hello World

The SQL training video suggests using an autoincrement field then selecting it as an Identity as the unique identifier and thus the Key field. However, it seems this approach is controversial in the SQL world. I appears the standard of practice is to use two fields as the unique identifier. Below is a link to a message board discussion.

This is important because it is the foundation of database design and so I would like to open it up for discussion since many of us have migrated to a SQL backend.

http://bytes.com/topic/sql-server/an...te-rows-insert

[glenschild]

Re: Identity Field Controversy

Hmmmm, interesting topic! I guess in the first instance it sort of reinforces the need to consider business rules at the forefront of the design stage for any application.

On occasion I have met and dealt with self proclaimed database experts who have a fantastic theoretical knowledge of database design but crumble as soon as the "real world" kicks in!

Having said that I don't as a general rule normally expose primary keys to the end user and the uniqueness of each record does not normally rely just on a primary key!

[koga101]

Re: Identity Field Controversy

Thank you Glen!

[frankbicknell]

Re: Identity Field Controversy

Having said that I don't as a general rule normally expose primary keys to the end user and the uniqueness of each record does not normally rely just on a primary key!

I second that notion.

[chadbrown]

Re: Identity Field Controversy

I have read on several forums that the best practice for keys is to use two primary keys such as an autoincrement like record_num but also add a timestamp as the second key. However I don't use this practice and have tables that are well over a million records without any problems using a single key such as record_num.

[jdusoe]

Re: Identity Field Controversy

I have been working with MS SQL server since version 6.5 in both desktop and web applications. As a result, I have come to certain "conclusions" about what works best for me.

The decision regarding whether or not to use an identity column as your only primary key is, as mentioned earlier, a hotly debated topic - and I have certainly experienced the gap between theory and practical application. The other gap that exists is the dichotomy that exists between the perceived needs/role of the DBA and the needs of the Developer. Of course, many Alpha developers have to fulfill both roles.

I most often use single fields keys for all of my tables, keeping in mind the following:

1. For tables that REQUIRE a sequential number for auditing purposes, use identity columns. You could roll your own process to manage sequential IDs, but identity columns just work if your design is correct.
2. For all other tables OR if you require any offline data entry, use GUIDs. The reason for this is multiple.
- GUIDs ensure uniqueness and makes replication management MUCH easier.
- GUIDs can be generated via the database or via Alpha, whichever works best for your particular application
- When using SQL server for websecurity, you can set the ULINK field to be the same at the User GUID. Using the security framework provides a one step method to obtain the ULINK value without really having to think about it.

The main reason I use single field keys is that you can build generic processes and methodologies to handle your data - which was a necessity for much of my earlier desktop development. Since I use quite a few stored procedures and triggers in my systems, it is very easy to follow the code and understand the logic flow when my keys are handled consistently throughout the application.

Although some tables may not have a "natural" key, the fact that I only have to deal with one or two methods of handling keys makes for a much faster (translate: more profitable) experience as a DEVELOPER. On the other hand, it is important to be sure to understand when to properly normalize (and DEnormalize when it suits the application) as well as understanding when best to use stored procedures and triggers - enforcing business rules at the database server rather than the presentation layer. And there are several decisions in the application design which ensure no garbage gets into the system - not the least of which is the rather extensive data validation you can perform using Alpha's built in tools.

The thread you linked to is an excellent example of someone with a great deal of opinion which seems to come from the theoretical, computer science, DBA's view of the world.

To put my opinion in perspective, in over 20 years of database development I have used single key fields successfully in many applications:

• Hospital Physician Comparative Reporting
• Manufacturing
• Inventory Control
• Call Center Management
• Credit Card Merchant Accounts
• Document/Image Management Systems
• Epidemioligy Consulting
• Network Monitoring
• and many others...

Many of these applications have tables with millions of records per table. Whether on a desketop, SQL Server or Oracle database I have never had a single problem with this approach - but maybe I've just been lucky. :)

Hope this is helpful...

[frankbicknell]

Re: Identity Field Controversy

I agree that you need only 1 primary key to maintain millions of records. In MySql you can only have 1 primary key. The way I interpreted Glen's comment is that you use the primary key for the behind the scenes processing like deleting and linking not exposing it to the end user. In most of my tables I have other fields that are also unique that are exposed, like invoice number, job name, estimate number, vendor reference number, item number, serial number, etc and I rely on those for filtering, searching, sorting, etc.

[koga101]

Re: Identity Field Controversy

Thank you everyone, this is helpful to have on the message board.

I wanted to add that through linked grids I had created a pseudo-relational DB using DBF's but had to use two UI's because numbers would be re-used after a records was deleted (maybe there was a fix using Xbasic- I don't know). This method was not special, lots of developers did the same. However, like MS Access, SQL Server does not re-use a number once it has been deleted. So no need for this methodology. This would support fearlessly using a single UI.

Alas, for the moment the universe is again a harmonious place