redesigning my database

On a old web app I had a table with the following structure:

tools(id, name, abbreviation, width, height, power, type)

The thing is that I need to add history support to the tools. The working scenario is that details of each tool can be modified, and I need to keep track of the changes. For example, if I need to see the details of a tool fr开发者_如何学Goom a certain date from the past, how can I do that?

How the database have to looks?

---

I would keep the same table, but add a dateEffective field, and then create a new row every time a tool changes. Then when querying the table, you can get the version of a tool at a given date using:

SELECT * FROM tools WHERE id=@id AND dateEffective<@thisDate ORDER BY dateEffective DESC LIMIT (0,1)

EDIT You may wish to create another field called toolVersionId as a primary key.

EDIT 2 In response to your comment, how about:

SELECT *
FROM tools t1
WHERE toolVersionId IN (SELECT toolVersionId
                        FROM tools t2
                        WHERE t2.id = t1.id
                        ORDER BY t2.dateEffective DESC
                        LIMIT (0,1)
                       );

---

there are probably many ways to do this. One way I have had success with in the past would be to add a version_id column to the table. So the combination of id and version_id becomes the full key for any specific version of a tool. Note that with this approach it is very easy to get all versions of a particular tool, you just select on the id. Getting the current version of the tool is also easy, its just id, max(version_id).

A downside of this approach is you need to write code to manage the versioning.

---

Create a ToolsVersions table with all the same columns, only add a timestamp as well. Every time you change tools, save the old values to the ToolsVersions table with the current timestamp. Then, you can fairly easily query for all versions between a certain range, or find the version nearest to a desired date.