Best approach for generating column values based on record count.

Question

I would appreciate any thoughts about the best approach to handle generated columns based on the primary key idendity column.

I have a main record table with (amongst other columns) an auto-generated integer primary key column "RecID" and a text column "RecNum" which is of the form "YYYY-count" where "YYYY" is the financial year and "count" is the number of records in the current financial year. e.g. "2223-147" would be the RecNum of the 147th record created in the 2022-2023 financial year (our financial years begin July 1). The internal RecID is never exposed to users, who are used to the "YYYY-count" labelling from when the system was an old paper/spreadsheet system.

To create a new record, I have a stored procedure which inserts a new record in the main record table and then updates its RecNum by querying the RecID of the first record added in the current financial year and subtracting this value from the RecID of the newly added record.

Very simple (don't have to store internal counts etc) and works well. (Note for audit purposes, records can never be deleted, just flagged as hidden, so I don't have to worry about gaps in the RecID's).

However, there has been a recent requirement to import a bunch of legacy records (from previous financial years) from the spreadsheet into the database. This will obviously throw out the above calculation. e.g. if I import 500 records, then the next time a user adds a record via the web application, the RecID will have been incremented by 500 and the count of records this financial year will be 500 too many.

I'm a veteran C++ programmer, not a SQL expert, so forgive any obvious errors. In order to solve this problem, I've thought of a few different mechanisms:

1) Keep an "ImportCount" offset in a separate config table which the stored procedure queries and subtracts from its calculation.
2) Import the legacy records with RecID's of negative numbers (using IDENTITY INSERT control) so there is no overlap.
3) Keep a "YearCount" running total in a separate table which is incremented each time a new record is created and use this value instead of basing it on the RecID offset.

The import count from (1) is the number of records manually imported this financial year. They already have legacy RecNum's which are given in the imported CSV file and so don't go through the stored procedure.

I don't really like any of these solutions are they aren't elegant. (1) and (3) need messy reset mechanisms to reset back to zero at the start of each financial year and (2) just seems like a hack.

If there is a best practice approach to this type of problem, I'd appreciate any tips. Thankyou!

Answer 1

Hi MarrinerDev-4693 ,

In this situation, Why do not you keep another column to keep year wise recid's, that way, you can easily see if a new year starts, the id of that column starts with 1, That way the main Primary columns will stay, and new column will also serve the purpose.

For existing data, one time you can write a script to have the yearly id's as per existing, for future, it can be maintained easily.

Hope this helps. Do mark as answer if you find this suited to your needs. Thanks