Re: primary key - tag numbers

From: Bernard Peek <bap_at_shrdlu.com>
Date: Sun, 23 Feb 2003 23:11:36 +0000
Message-ID: <s$cZ7WMoUVW+Ewmu_at_diamond9.demon.co.uk>


In message <cc15b354.0302230746.5a9abfe5_at_posting.google.com>, tretasdehr <tretasdehr_at_yahoo.co.uk> writes
>In a database of animals at a large shelter, the tag number of the
>animals can be large, but it will still need to be repeated after
>about 2 years. That is, the tag number will be used again.
>
>If the tag number is to be used as the primary key in a (SQL) database
>that has to preserve its records for legal reasons, what is the best
>way to keep the primary key unique and prevent it from repeating with
>the re-use of the tag number?

It can't be the primary key in a table unless you purge the record with the same number before you create the new one.

You could create a working table that never has two year old data, you could keep 1 year's data and write older data into a history table. The history table can have other fields (such as the year the tag was created) as a compound key.

>
>Could it just be prefixed by the last 2-3 years of the date, eg 02---
>or 002--- ? Since that would be tedious to write every time, can this
>year information prefix be entered automatically in a SQL database, so
>that all the user needs to do is to enter the actual tag number? That
>is, if the user enters a number like 40023 then in the actual
>database, that is recorded as 00240023. However, when it is recalled,
>it is just shown as 40023.

That's perfectly possible. I find that it's often a good idea to record the precise date and time each record is created. It helps with debugging. If it's possible then you may like to do that.

-- 
Bernard Peek
bap_at_shrdlu.com
www.diversebooks.com: SF & Computing book reviews and more.....

In search of cognoscenti
Received on Mon Feb 24 2003 - 00:11:36 CET

Original text of this message