How to store old version of ID String in Database Design?

Question

I am building a small database for a lab. We have some rules to make a ID String for every Item, so i do not want to store it in my database. The problem is that some times changes in data, for example the person response for that item changed, causes the chang of ID String. But i can not correct it for printed docs. How can i store the old version of that ID String. I may simply do't change it but that will break the rules. Any suggestions?

Answer 1

for example the person response for that item changed, causes the chang of ID String

Looks like the workflow in your lab is broken. IDs should never change. Try to bring this to attention of your superiors.

Answer 2

It is important to distinguish between a primary key which is supposed to uniquely identify a row in a table and some kind of a smart key that products in catalogs usually have.

For a primary key use auto-incrementing integer -- very few exceptions to this one.

Add columns for things that you are trying to represent in that smart key, like: Person, Project, Response etc.

Add a separate column for that key and treat it like any other field in the table -- this should keep people who are used to this kind of thinking happy.

Smart key is a misnomer here, from a db-design point, that key is rather dumb.

Answer 3

To expand on Damir's point

A "Smart Key" is what you say when

We have some rules to make a ID String for every Item

You're taking the name of the item, maybe a category code and adding

person response for that item

So if I were responsible for Beakers that item ID might be

GLASSWARE-BEAKER-SPAGE

That 'code' becomes a 'Smart key' when you use it in your database as a Primary Key.

This is an anti-pattern. Like most anti-patterns it's seductive. People like the idea of just looking at the key and knowing what kind of thing it is, what it is called and who do I ask to get more. All that information on a report or shelf-label with just a few characters. But it's an anti-pattern for the reason you mentioned - it has meaning and meaning can be changed.

As Damir suggests, you can store this value in another column that we'd call an ALTERNATE KEY or CANDIDATE KEY... it's unique, it could be a PK but it's not. You'll want a unique constraint on the column but not a Primary Key constraint.