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.
精彩评论