Record ID's

[sparkbyte]

What are some of you using instead of the normal AutoNumber??

I am not particularly happy with the normal Autonumber field for creating a unique ID. Maybe a calculated field using the date/time and the Autonumber somehow.



Thanks

John Fuhrman
faq329-6766
thread329-1334328
thread329-1424438

 

[markros]

IMHO, Identity field in SQL Server is the simplest and most efficient way of creating a PK.

PluralSight Learning Library

 

[gmmastros]

I am not particularly happy

Can you explain why you are not particularly happy?

There's a ton of information available about this, and developers tend to have rather strong opinions about it too. I recommend a google search on:

[google]Natural key vs. surrogate key[/google]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

This is just me... But I have a tendency to see autonumbering (ie. 1,2,3,4,5,....) being to similar to row numbers. I prefer to not have numbers in forms like 000001, 0000002, that the users see as Record_ID, invoiceID or something similar. So I was wondering what some of the SQL developers on Tek-Tips prefer because I have a very high respect for the people here and I have never been disappointed with the creative and direct answers I have always received.

Thanks

John Fuhrman
faq329-6766
thread329-1334328
thread329-1424438

 

[RiverGuy]

Quite often, users do not see the Identity column values at all. But I think what you are asking is, should the need arise to have the database engine create a unique key which would be displayed to the user, what are the options?

Besides an Identity column, another possibility is a UNIQUEIDENTIFIER column. But these are not very nice to look at and they are hard to memorize the characters therein. But it's another easy way to get an automatically generated value.

Maybe a calculated field using the date/time and the Autonumber somehow.

You could do this, but you don't need a calculated column. You can have an Identity column, and a DateTime column, and either return them together in your SELECT statement for display, or have the application concatenate them together.

For example, if you had two columns, with values of '20100120' and 5, then you could have your application display '20100120-0005.'

If you really need to, you could forgo the Identity column, and store an integer for the count of the day--meaning you would start back at 1 each day.

 

[sparkbyte]

What I have been thinking is to have a UNIQUEIDENTIFIER column and another for the displayed Record ID. This way the user would see a sensical Record identifier and the UNIQUEIDENTIFIER be used behind the scenes for relationships.

Am I incorrect on this thinking??


Thanks

John Fuhrman
faq329-6766
thread329-1334328
thread329-1424438

 

[gmmastros]

What I have been thinking is to have a UNIQUEIDENTIFIER column and another for the displayed Record ID. This way the user would see a sensical Record identifier and the UNIQUEIDENTIFIER be used behind the scenes for relationships.

This is almost exactly what I would do.

But, instead of a unique identifier, I would use an integer identity.

My application involves students. Every school needs to track their students for various reasons. Each school assigns a student id number to each student. I could have used this value for the PK in the student table and for the FK in other tables. Instead, I decided to use an identity column (StudentId) and have another column (StudentIdNumber). There is not one single spot in my application where the user sees StudentId. It is completely meaningless to them. All they ever see is StudentIdNumber. I'm really glad I made this decision too. You see, a couple years later, a customer said they needed alpha-numeric student id numbers. No problem, just change the data type. Someone else needed to change the student id number. Again, no problem. It's only stored in one place and is easily changed. Nobody will ever tell me they need to change the StudentId because they never see it.

I'm a big supporter of surrogate keys that are integer identities.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JoeAtWork]

I agree 100% with George. The Primary Key's only job is to be unique within the table. An integer identity does this most efficiently.

My philosophy is that the users should never see the primary key - it's for internal housekeeping, not part of the data they are interested in.

What I have been thinking is to have a UNIQUEIDENTIFIER column

When you are considering a design change, the question you need to ask yourself is what benefit you get from the change. If you can't answer that question, then don't do the change. You haven't stated a requirement (such as replicating databases) that justifies using the less efficient UNIQUEIDENTIFIER over an identity column.

 

[sparkbyte]

This thread has gotten far enough that I feel I should elaborate on what I have been tasked with to provide some insight on where I am coming from on this.

I have been tasked with a project to move as many of the accessDBs in use here over to MS SQL. At last count there are over 200 MDB files on the network shares. Of those about ¾ of them have data that will need to be archived.
Out of that ¾ about 20 are the primary Access UIs that will need to be converted to use the SQL server rather than local tables.
What happens is that when the AccessDB grows to about 2GB they make a copy of the application and rename it to something like 20100101-MyApp.mdb then truncate the primary data table and start using it again.

The AccessDB and UI that I am working on now is one of the 3 most used and pivotal in the center. Many other Access UIs get data from it for reports. So right now I am looking at how I can restructure the underlying data (normalize it) and what impact will that have on all the rest of the AccessUIs that rely on this data.

This DB has about 2 years of archival data is over 3 million rows so far and I still have about 10 archival MDBs to import. With the DB being a central point the PK structure has me concerned. Right now the DB is basically flat with a contacts lookup table.

Hope this doesn’t confuse the matter too much…


Thanks

John Fuhrman
faq329-6766
thread329-1334328
thread329-1424438

 

[danvlas]

You may need a field that identifies the source database (by name, number, whatever) and a field that stores the source ID of the original record.
These two fields could make a unique clustered index in SQL Server.

Nevertheless, the primary key of the table could still be an Identity field, managed by the database engine.

I hope I understood your need correctly...


Daniel Vlas
Systems Consultant

 

[kss444]

In your access database what are the current PK's? I don't see why you can't create a sql server DB then populate it with your access data then set the PK of the sql server table to be the same.

That way any application or access form that relies on the access PK's hopfully wont break.

Ordinary Programmer

 

[sparkbyte]

kss44, look at my 1st posting here thread702-1587213.

I have already done what you are saying. Because of the scope of use this DB is getting it is becoming apparent that just "upsizing" it won't be the propper solution.

The above thread also had the table structure as it is currently pulled from the SQL create statment for referrence.

Thanks

John Fuhrman
faq329-6766
thread329-1334328
thread329-1424438

 

[sparkbyte]

Daniel, excellent suggestion. But because of how they currently archive the older data the recordIDs associated with they are rendered useless so I am just assigning a new unique ID to them during the SSIS import.

BTW, I am testing this against both SQL2k and SQL2005 servers.

I have found that an Access 2003 application/project loses the SQL Design tool when the database source is on SQL2005.
This seems to be the only hindrance though.

Anyone else have experience with Access 2003 and migrating the data to a MS SQL 2005 backend?? Currently what I am doing is using the SQL2k server and then copying the queries, functions, and stored procedures onto the SQL2005 server just for ease.



Thanks

John Fuhrman
faq329-6766
thread329-1334328
thread329-1424438

 

[JoeAtWork]

Anyone else have experience with Access 2003 and migrating the data to a MS SQL 2005 backend??

I've done this with 15 to 20 different databases. I usually use the Access Upsizing Wizard to do the majority of the work. If working with a properly designed database (relationships in place, primary keys on all the tables) I find it does a good job of transferring over the correct schema design and all the records (I usually check the record counts before and after the upgrade).

I have not had any problems upsizing to SQL Server 2005 - pretty much all my database upgrades have been on this version.

You sometimes need to do a little retrofitting. What usually becomes very inefficient are complicated queries, especially highly nested ones (a query based on a query based on a query, etc.). I usually refactor these queries as stored procedures on the SQL Server. On the forms that the queries are based on, I write code to call the stored procedure and retrieve the data to a disconnected ADO recordset, I then bind the form to the recordset. You need to write additional code to do updates, inserts, and deletes, if you use this approach.