Creating a complicated primary key

[nosferatu26]

Hi,

I have a table called "tbl_Design_Change_Item". What I want is for the primary key to concatinate three separted strings into one and also auto increments. An example of what the first record ID should be is "SWCDCR2015_08_31_001". When a new record is created, I would like for the field to get a timestamp of the date and also increment the last digits.

It may be better to have a simple number as the primary key but then I would like for another column to contain this data.

I am pretty stumped right now. I have been trying to mess around in expression builder with the now functions and such.

If anyone can help me out with this it would greatly be appreciated.

 

[jdraw]

Use an autonumber primary key provided by Access. (known as a surrogate key)

If your other 3 fields represent separate concepts, then make separate fields of them.
Then make a composite unique index in order to prevent duplicates of the combination of the 3 fields.

No need to make your keys too complicated.

Good luck.

 

[The_Doc_Man]

Absolutely agree with jdraw, with the following amplification.

Make a synthetic PK via autonumber and remember that you DO NOT CARE what it actually is - you only care that it is unique. Then, when linking objects together via relationships, use that autonumber key, which is of type LONG (32-bit integer) as the FK.

When you store what you wanted to use as the PK, you can do it in three fields, one of which you supply from whatever it is (your "SWCDCR" portion), one that comes from the system clock reformatted (e.g. Format( Now(), "yyyy-mm-dd" ), and one that comes from a DCOUNT of the number of records already having the first two parts of that key - then add one to it.

Now you can make a composite index as JDraw suggests. But here is WHY you want to do it with a simpler autonumber... the bigger the key, the more space it takes up in the index. If you actually use that long sequence as a PK, you ALSO will probably have to use it indexed as an FK in at least a few cases. Using a LONG (4 bytes) takes up a LOT less key space for those extra indexes than a key that takes up 20 bytes (6 text + 10 character date string + 4 character unique number). You won't be able to avoid the long key for the parent table if you make a unique index for it, but you won't have to repeat that index on any other table.

 

[ButtonMoon]

here is WHY you want to do it with a simpler autonumber... the bigger the key, the more space it takes up in the index. If you actually use that long sequence as a PK, you ALSO will probably have to use it indexed as an FK in at least a few cases. Using a LONG (4 bytes) takes up a LOT less key space for those extra indexes than a key that takes up 20 bytes (6 text + 10 character date string + 4 character unique number). You won't be able to avoid the long key for the parent table if you make a unique index for it, but you won't have to repeat that index on any other table.

Don't make your decisions about keys based on size - that's just folly! I think we are talking about a Jet/ACE database here which means by definition that the sizes involved are tiny and the storage costs utterly trivial. If your database is larger than 1GB then you should be considering moving away from Jet/ACE anyway.

What I think Doc Man is really alluding to is not size but performance. He's assuming that a smaller key will always make your queries perform better. That's just not true however. If you are careless enough to add an extra autonumber key every time the alternative is larger than 4 bytes then you are practically guaranteed to end up with a database that is both over-complex and performs extremely poorly because it requires so many joins to get useful results out of it.

Too often I have seen tables with 2,4 or even 10 meaningless numerical foreign key columns that therefore require upto 10 joins just to make sense of the data! Those joins in many cases wouldn't be needed at all if not for the addition of the autonumber column to the parent table being referenced.

Adding unwanted autonumber columns on the basis of size makes no sense. What you need to do is be selective, evaluate each situation on its merits and only add an alternative key column where you find a real benefit to doing so.

Hope this helps.

 

[The_Doc_Man]

Button, I respectfully hope that we can agree to disagree.

You left out a performance issue that (unfortunately) can rear its ugly head - a slow network. Not all of us are fortunate enough to have 1Gb Ethernet (or higher). We have what we have. Since data in a remotely shared backend is a legit design or implementation possibility and since the "traditional" method of Access FE + Access BE is still common, the size of the components DOES matter.

Adding an autonumber key to simplify the JOINs to a single field simplifies the SQL statements. For inexperienced programmers, it is paramount to apply the KISS principle, and I don't mean the leather-clad, heavy-make-up rock group, either. Keeping it as simple as possible (or practical or effective) is a sine qua non for success in ANY design project.

Yes, I do indeed refer to performance. Remember that for a split FE/BE with a remote BE, you have to transfer the tables to the machine running the FE because that is where the MSACCESS.EXE image is running. That is where the action is.

If someone is blessed with a good network and limited contention, OK. Performance isn't so much an issue. If the database isn't split into FE/BE, then again, OK - performance won't be a big issue. But Button, you answered this question as though you thought your environment is the only possible one to exist. If you are on a slow network or your computer is a laptop with a single CPU and your budget was tight so you bought a low-end system, then performance and database size creep in a bit sooner than you might like.

As to having auto-number adding to the JOINs to make things more complex? I think you are exaggerating slightly here. A JOIN of the compound (3-field) key would be REPLACED by (not added to) the JOIN of a synthetic auto-number key. Joining 1 field is always going to be easier to manage than joining 4 fields - which is what you seemed to be saying or at least implying.

The only time you need the complex compound field is when you're displaying on Form or Report, not when you are just updating details internally. And even when making the compound name available, you aren't JOINing on the compound field, you are just referencing it. You wouldn't JOIN on it anyway because that would be a JOIN on a key that wasn't your PK. Further, the more JOINs you add, the longer it takes in terms of design work and design validation. Real-world problems have deadlines. Whacking away at a compound key (when using a single synthetic key lets you design/implement faster) just seems wasteful of your most precious resource - time.

nosferatu26, if you take anything away from my sidebar discussion with Button Moon, it should be that even experienced programmers can have different viewpoints and they can both justify their choices. So it will always be when there are "many ways to skin a cat" as we say in the USA.

You might ask, "Is there a sure-fire way to know which viewpoint is wrong?" Why, of course there is. The method that doesn't work for you is clearly wrong. The one that works best for you is clearly right. Anything else is a shade of grey. Let's just hope you don't have 50 choices.

 

[ButtonMoon]

Doc Man, I don't think you read or understood me. Like you I am concerned with simplicity and performance. Adding an unnecessary extra key is likely to increase complexity (because more joins may be needed in queries where none were needed before) and in many cases will hurt performance (queries are slower because of the extra joins).

This is just an obvious result of doing what you are suggesting. If you have a composite foreign key (x,y,z) in table A referencing table B and you replace x,y and z with a meaningless foreign key referencing table B then at some point you will be forced to join A and B to retrieve the values of x,y and z. The user/consumer of the data doesn't care about your surrogate foreign key reference. What the user wants is the data he originally had in A including x,y,z. You are forcing a join to be performed where none was necessary before. I don't know why you think this is an "exaggeration".