Advice on building a grocery database
Hi this is my first post, excuse me if I am in the wrong place.
I am trying to set up a database to track and compare grocery store purchases and prices. It is for my personal use only - meaning I will be the only one entering data and benefiting from this,,, maybe my wife too. There are many iApps out there already but none that I know of that run on my desktop.
Since I am new to this I am having difficulty deciding on the structure, but I have some ideas. In order to keep data clean I have read I should use value lists and populate them manually during the design stage. To me that seems like a lot of extra work up front, i.e. putting every possible item and variety I could possibly purchase from multiple grocery stores into a value list!
Some fields I think should be included:
store, department, item, house brand/branded, description, type, organic/inorganic, unit, qty, price, date
Some of the fields would have few enough choices that a value list would be sane; store, department, organic/inorganic, unit.
The others,,, how to build conditional value list for each of; item, description, and type without entering values up front, but as data is entered? Is this even necessary?
Another idea is scanning receipts and using data in the receipts to build up the data. This would be great since we have a ScanSnap which scans receipts
The next steps, charting the trend in prices for each store over season/time; calculating any savings from one store to the next. Shopping lists based on store? Factoring in membership costs?
I would appreciate any input, advice, general guidance on what's necessary and what's not.