I'm creating a small database for any lab. We've got some rules to create a ID String for each Item, and so i don't want to keep it during my database. However , some occasions alterations in data, for instance the individual response for your item transformed, causes the chang of ID String. But i am unable to correct it for printed paperwork. How do i keep old version of this ID String. I might simply do't change it out but which will break the guidelines. Any suggestions?
To grow on Damir's point
A "Wise Key" is exactly what you say when
We've got some rules to create a ID String for each Item
You are using the title from the item, perhaps a category code and adding
person response for your item
Therefore if I were accountable for Beakers that item ID may be
That 'code' turns into a 'Smart key' if you use it inside your database like a Primary Key.
It is really an anti-pattern. Like the majority of anti-designs it's sexy. People like the thought of just searching in the key and understanding what type of factor it's, what it's known as and that do I request to obtain more. Everything info on a study or shelf-label with only a couple of figures. But it is an anti-pattern for that reason you pointed out - it's meaning and meaning could be transformed.
As Damir indicates, you are able to store this value in another column that we'd call another KEY or CANDIDATE KEY... it's unique, maybe it's a PK but it is not. You will want a distinctive constraint around the column although not a principal Key constraint.
You should separate a principal key which is designed to distinctively identify a row inside a table plus some type of a wise key that items in catalogs will often have.
For any primary key use auto-incrementing integer -- very couple of exceptions to that one.
Add posts for things that you're attempting to represent for the reason that wise key, like:
Give a separate column for your key and address it like every other area within the table -- this will keep those who are accustomed to this type of thinking happy.
Wise secret is a misnomer here, from the db-design point, that secret is rather dumb.
for instance the individual response for your item transformed, causes the chang of ID String
Appears like the workflow inside your lab is damaged. IDs should not change. Attempt to bring this to attention of the superiors.