I have a database which among other things is used to keep track of jobs done on ships.
My [Job] table uses [ShipID] to pull in the relevant data from the [Ship] table.
Everything works fine, BUT occasionally a ship will change its name. This in itself is not a problem but for historical reasons I need to keep track of what the ship was called when we carried out a particular job.
At present, as well as recording [ShipID] in my [Job] table I also record [ShipName] which of course is duplicating data
As I say, this works but deep down I know it is not the way I should do it. However, I cant figure out how I should, suggestions greatfully received
In such a scenario, you are not duplicating data by copying the ships name to the jobs table. In one table you are storing the ship's current name. In the other table you are storing the name of the ship at the time the job was performed.
These are actually distinct pieces of information, since obviously one can change without affecting the other.
So, as in Andrew's invoice example, you are not violating the principles of normalization by copying this data.
If it's not practically useful, then it's practically useless.