Mysql – Warehouse Database Design Question

database-designdatatypesmysql-5.5

Apologies if this has been answered previously but I have been unable to get a specific model to work.

I have a database table that needs to store some very basic info for a reporting system. The information fed to me is Date – Pallets-in – Pallets-out – Pallet_Count – Man_Hours

The report when created needs to show Date – Pallets handled (Pallets-in + Pallets-out) – Man-Hours – Avg PLTS per hour – Overall Pallets in warehouse

EG yesterday report might shows 14-03-2015 PLT-Handled 400 (300 in 100 out) – Man-Hours 36 – Avg Plts p/Hour 11.11 – Overall Plts inW/House 7300 (day before there were 7100 so add 300 in and remove 100 out)

I currently just compose this report manually but I want to try and do it in a database so we have better historical reporting ability. Do i just have a table that stores every part or would it be better to calculate some parts in the report generation. (I am unsure how to mainly include the Total pallets in warehouse as it calculated from a starting figure then add plts in minus plts out.

Hope the above mmakes some sense

Regards

Paul

Best Answer

You are best to start with a transactional database that records the data in as much detail as you can get from the source. You can always summarize and report from detailed data, but you can't go backwards from summary information to detailed information.

If you are getting data in the form:

  transaction_date  datetime
, pallets_taken_in  int
, pallets_sent_out  int
, total_work_effort decimal(19,2)

Then create a table with these columns. If you get one record per day then make the transaction_date a unique index (or even primary key).

Calculating the values for your report is easy from these columns:

  • Pallets handled = pallets_taken_in + pallets_sent_out for a given transaction_date

  • Pallets/hour = (pallets_taken_in + pallets_sent_out) / total_work_effort for a given transaction_date

  • Pallets on hand = SUM(pallets_taken_in) - SUM(pallets_sent_out) over all records in your table. (i.e. over all dates)

Note that depending on how you key your table, you can also include physical inventory adjustment records to make allowance for correcting your balance on hand.

Depending on how much data you have and how much (and what kind of) reporting you need, it might make sense to pre-calculate some of these values and store them in a second table in a data warehouse type of scheme, but don't pre-calculate data unless you're pretty sure you need to. Having two copies of the same data (raw transactions and data warehouse) means that you have potential discrepancies to manage.

Related Question