Best parctice - Auto increment or Natural Key
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...