Go Back   Access World Forums > Apps and Windows > SQL Server

Thread Tools Rate Thread Display Modes
Old 06-07-2019, 03:24 AM   #1
Newly Registered User
Join Date: Apr 2012
Posts: 24
Thanks: 6
Thanked 0 Times in 0 Posts
Geirr is on a distinguished road
Best parctice - Auto increment or Natural Key

Hi all.
I've tried to read throug lots of docs on best practice on PK on tables, but....

For electro company in shipbuilding I'created a backend with several tables, one accdb for each project. Many of exisisting keys in those table are same for each project - due to what they call SFI in ship bulding.
Typically, each 'PK' in access tables have a meaning in the understaning of what it represent, eg. 408.1001.10 (Believe me, this number has a meaning...)

Now, I'm transferring everything to a common SQL server, and I started of using Autincrement fields as PK, due to fact that those where easy to connect n relation, and becaus I where told this is the perfect way. But, it gets more and more clear that using 'meaningfull' ID's in PK would give me better info in stead og 'random' integers.
So, adding a Project indetifier here - normally 5 digits/char would create an PK-info like '12345-404.1001.10' in one of the table. The PK field-info will be generated normally in a field's AfterUpdate.

The question is; will I get big difference in search speed, updates etc using PK with character(up to 30 chars)? Using char-based PK as mentioned gives us a big 'reading' benefit, but I'm afraid that we will loose other important benfits like speed. (search, update etc). Googling this issue have until now given me 50/50 pros and cons each way...


Geirr is offline   Reply With Quote
Old 06-07-2019, 05:11 AM   #2
June7's Avatar
Join Date: Mar 2014
Location: The Great Land
Posts: 2,395
Thanks: 0
Thanked 558 Times in 554 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Best parctice - Auto increment or Natural Key

Here we go again. Review https://access-programmers.co.uk/for...d.php?t=305302

You can still have the natural value in table and use the autoincrement as the PK/FK. Purpose of autoincrement PK/FK will be to associate records in queries and linking form/subform and report/subreport. Users don't even need to be aware of them.

Searches can be designed so that user selects the natural value but code will use associated autoincrement.
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto Increment jimsterman82 Forms 15 04-17-2019 05:15 PM
Auto Increment Value padhuka General 6 06-18-2018 09:25 PM
Auto Increment BobNTN Tables 2 01-29-2008 11:34 AM
auto increment? Tech General 1 03-25-2005 08:18 AM
auto increment darcy Forms 1 10-27-2004 11:15 PM

All times are GMT -8. The time now is 06:14 AM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World