Mysql – Track price changes on a daily basis

MySQL

I am trying to track price changes on a daily basis. I have 2 tables Stores and Prices.

Table Stores columns:

s_id
company
address
city
state
zip
phone

Table Prices columns:

ref
storeid
price
mgr
datetime

NOTE: storeid in Prices is a reference to s_id in Stores

What I would like to do is run a query to find all prices from yesterday and compare them to today to see if they have changed. Please understand that I am filing in for the database admin for any advice you can offer.

Best Answer

Would be something like:

select  p1.ref, p1.storeid, p1.price, p2.price
from    prices p1 
join prices p2 on p1.ref = p2.ref
where   trunc(p1.datetime) = trunc(sysdate)
and     trunc(p2.datetime) = trunc(sysdate-1)
and     p1.price <> p2.price;

You can add Stores to get store information if you need. I do not know which DB you use so I gave a solution for Oracle. The trunc function gives you the date part of a date.

Related Solutions

MySQL – How to Track Database Schema Changes

just a couple of minutes ago I was checking this: A table that should exist in all projects with a database, seems simple enough to put in practice, check it out:

It’s called schema_version (or migrations, or whatever suits you) and its purpose is to keep track of structural or data changes to the database. A possible structure (example in MySQL) is:
create table schema_version (
     `when` timestamp not null default CURRENT_TIMESTAMP,
     `key` varchar(256) not null,
     `extra` varchar(256),
     primary key (`key`)
) ENGINE=InnoDB;
insert into schema_version(key, extra) values ('001', 'schema version');

Whether you add this table from the beggining of the project or just after you’ve deployed the first version to a staging or production server is up to you.

Whenever you need to execute an SQL script to change the database structure or perform a data migration you should be adding a row in that table as well. And do that via an insert statement at the begining or end of that script (which is committed to the project’s code repository).

…

Mysql – Concept to keep track of changes in all tables

The answer depends on what you intend to do with the data. Are you going to be doing frequent queries against it or do you only need it for the occasional lookup of what happened when, to rollback bad changes, or for regulatory reasons?

In the first case above, use the second process you descibed although I would add a column to denote the active record. I would also add a record created date. I wouild also create a view of just the active records and use that for allof my code that needs to see the current data.

In the second case above, I would have audit tables that are populated through triggers (the offective way to create audit records for any change to the db) and that also include the data of the change and the user or application that made the change as well as the old and new values.

Best Answer

Related Solutions

MySQL – How to Track Database Schema Changes

Mysql – Concept to keep track of changes in all tables

Related Question