In my opinion you have two, partially separated questions: How to identify a record and how to make it unique. Also you clearly want to use an identification which is meaningful to the user.
In order for your program to identify a record, the general suggestion is to use a
Surrogate key - Wikipedia[
^]. Since this kind of key is not derived from the data there is no risk that the value would need to be changed if the data or the requirements change.
To generate a surrogate key, you can use for example an
IDENTITY[
^] or a
SEQUENCE[
^]. If the system you're building is going to have multiple databases then an
uniqueidentifier[
^] may be a suitable choice. By defining this column as a
Primary Key [
^] you ensure that only one value can exist. in a table
What comes to the key visible to user, you can use
Natural key - Wikipedia[
^]. To ensure that there is only one record in the table you can define also this as unique using an
Unique Constraint[
^].
In your example you actually had two elements which together would be the natural key, the year and the running number. These should not be stored in a single column like you described. Instead you should use two columns, one for year and the other one for the ordinal. Making these to together unique guarantees you the correct key.
In your question you wanted to show these two items in a single column separated with a slash. Since the two items are stored in separate columns this should be done either in the SQL fetching the data or perhaps preferably on the clients side. To do this in SQL, consider the following example
select cast(a.year as varchar) + ' / ' + format( a.ordinal, '00000')
from ( select 2020 as year, 1 as ordinal union all
select 2020 as year, 2 as ordinal union all
select 2020 as year, 3 as ordinal ) a
This would give
2020 / 00001
2020 / 00002
2020 / 00003
Submit an article or tip