my only advice would be to keep your "historical" tables separate from the tables used for "current information", even if this means duplication of data. if you have foreign key or application dependencies between your historical data and your current data, its much more tedious and difficult to clean out old information from the history tables. its nice to be able to freely delete or remotely archive portions of data in the historical tables strictly via date ranges, and even to disable foreign key constraints in order to speed the deletion up, without any concern for the application failing.

On May 20, 2006, at 12:34 PM, Koen Bok wrote:

Maybe this is more a database related question, then a sqlalchemy question. Never try never know.

I am building a point of sale application based on sqlalchemy. To keep things organized, we keep a log of changes on some tables like orders, products, stocks.

Let's take stocks in this example. If we have a stock of 20 from product x at this moment, I would like to be able to see two things:

- What was the stock of product x total 36 hours ago
- What happended to the stock of product x in the last two weeks, who made changes?

And for an order
- What did the order look like 3 days ago

What we could do is set up a logtable for each table we want to track and save all values on a change (with a server side trigger) plus the person responsible. This way we have to design a lot of custom tables for each table we want to check, and write the functions accordingly.

So I was thinking if there already was a "right way©" to do this. It should be a fairly common problem, so maybe there is a standard solution.

Thanks,

Koen



-------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid0709&bid&3057&dat1642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users

Reply via email to