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

If you really need to know that, then your solution may be the most reliable way; it's not unlike recording a product's price as well as its ID on an invoice line.

An alternative is to have a ship_names table something like:

Now you can look up the ship's name where the Job date falls between the from_date and to_date.

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.

Gentlemen,

Thankyou.

When it is explained it does seem obvious really. However, I have always felt that taking two pieces of data from the same table in this scenario was somehow 'wrong'.

It is nice to have my mistake cleared up and to put this particular niggle behind me.

Again, thankyou.

Definitely not obvious. Its a mistake made by many people who apply the principles of normalization without really understanding the purpose.
But now you know!