Re-numbering unique key based on calendar year

[Pawn28]

Here is my dilemma:

I have an Access DB and a VB.Net form I've created.
An important part of this program is a Tracking number I've created VB side. The format they have asked for is a sequential number followed by a year, like this 1-2005, 2-2005 ect.
How I created this number is by reading in all the rows from the DB into a Dataset.
I then count the number of Rows and add one to this for the new entry.
They would like it changed however so that it starts re-numbering at 1 with each new year, like 9999-2005, 1-2006, 2-2006 ect.

I really don't know how I can do this. Any ideas?

 

[ThatRickGuy]

I preach this one a bit too. Your primary key should have no logic. None. Just an auto incrementing integer field. If you want to have another identification field that is logic containing, great, but don't make it your primary key.

For your situation, I would recommend 3 fields. The auto incrimenting numeric primary key (which your customer will never have to see). The Yearly Index (the number that resets to 1 every year). And the Year of the record.

Use the primary key for any relations, and when ever the record needs to be identified to the user, show them the Yearly Index and Year of Record together.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[Pawn28]

Hi thanks for the info. I should have clarified that my tracking number isn't my DB's primary Key. It is just a number that the users would like to use in order to query the DB. Another issue that I forgot to mention (and I apolize for this) is that these numbers re-sequence ever year but not based on our system year but based on the year that the user enters. So I could have entry 1 needing a "Tracking Number" of 1-2005 and the second entry having 1-2006 while the thrid could be back at 2-2005.

 

[RiverGuy]

I think Access supports calculated fields. I would try this. Make your calculated field by concatenating the auto-incrmenting field along with the YEAR() value of your accurate date field.

BTW, I'm not sure what you mean by primary keys having no logic, Rick. If you're talking about special coding, then that would be right for most cases, IMO. Otherwise, I don't need a primary key to always be an auto-incrementing field. It can be a date if a table has one record per date value (date or datetime), or a VIN number (even though these may have logic to someone else, it would make the best key for a car dealership's inventory database).

 

[ThatRickGuy]

(even though these may have logic to someone else, it would make the best key for a car dealership's inventory database)

I disagree. VINs, dates, calc fields etc depend on business rules. Business rules can change, mistakes can be made. I've ran into way to many headaches tracking down issues with keys based on business logic.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[RiverGuy]

It doesn't matter if business rules for creating VINs change. They are still unique. If a user keys in the wrong number, they simply change it, and the related tables are automatically updated.

If you use an identity field for a primary key which takes on meaning, what happens when you must restore an older backup? Do you have to remember which order the missing records were entered in so that you can get them back in the table with the correct key?

 

[ThatRickGuy]

If you use an identity field for a primary key which takes on meaning,

The primary key has no meaning, the users never need to see it. They can see the logic containing identifier that is specified by the business rules.

what happens when you must restore an older backup? Do you have to remember which order the missing records were entered in so that you can get them back in the table with the correct key?

The old backup already contains the key, so why would I have to worry about order?

It doesn't matter if business rules for creating VINs change. They are still unique. If a user keys in the wrong number, they simply change it, and the related tables are automatically updated.

As far as pure data relations go, yes, it's fine. But realisticly, there is significantly more then that. Lets say your logic identifier is YY-xxxx. Like 99-0001, 99-0002, etc. You system displays listings of items that is sorted by your primary key (which contains logic). That works until your boss decides that they need to track the 4 digit year. Easy enough, you set the system to use the full year (YYYY-xxxx) but now new records (2005-0001) are showing up before old records (99-0001). Which means you will now have to go through your database and update all of the old primary keys to match the new business logic. And depending on how your relationships are set up on the tables, that can be a massive headache. Even if you don't have referencial integrity turned on it still means running SQL against all related tables.

Now, if you have an identity primary key, and the logical identifier that the user see split as two columns (date and itenditifier), all you need to do is change the format of the date portion of the logical identifier that the user sees. A quick change in the format of a text box and you are done.

Which would you prefer?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[RiverGuy]

The old backup already contains the key, so why would I have to worry about order?

OLD backup, as in before a catastrophe happened, when its your only option to restore something when current things don't exist yet.

We're not debating whether or not to construct a primary key out of a year code. We're debating whether or not you need an identity field as a primary key for every table, all of the time. It's simply not the case, especially if you have multiple installations of the same database. I'm sure you would prefer it if a college used your student-id as the primary key? It simply makes more sense than storing your student-id along with record numbers.

Maybe I misread your post. I thought you were championing using an identity field for every table. You may have just been speaking to this particular table the poster is referring to.

 

[ThatRickGuy]

I'm sure you would prefer it if a college used your student-id as the primary key? It simply makes more sense than storing your student-id along with record numbers.

Absolutely not! Student IDs are recycled at my school. Using them as a primary key would destroy the integrity of historical data.

I'm also strongly against using sensetive/secret/confidential information as a key (ie: SSN, Drivers License Number, Credit Card Number, etc).

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[chrissie1]

me I like the identity fields too, in every occasion. But some people like them and others hate them. I can see avantages and disadvantages in both systems. Do whatever you like best and stick with it.

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[ca8msm]

Personally, I always do have an auto-incrementing value as a primary key on each table. I'm not sure if it's the best database design but it hasn't ever caused me any problems in the past (and as a result I find that I always use it in new systems).

The only disadvantage I can see in using this technique is the extra storage space for this field which should be negligible (and I can think of more advantages than disadvantages which is perhaps my reasoning for using it!).

Saying that, I'm not sure that I agree with Rick's school using recylced student ID's (even if they can be uniquely identified by the primary key).



____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[ThatRickGuy]

Saying that, I'm not sure that I agree with Rick's school using recylced student ID's (even if they can be uniquely identified by the primary key).

My school has some of the worst designed student/class tracking software ever writen. What used to be a 1 campus electronics engineering school is now an 8 campus school with accredited degrees in CS, Networking, MIS, CAD, Graphics, game development, and a slew of medical records and nursing degrees. Their tracking software/system has not been updated since its original design about 10 years ago. Needless to say it has it's limitations. Luckily, they have some former students who they hired on after internships for some cheap development. And things have improved significantly since.

-Rick



VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.