The Real Junkfood Project consists of a network of pay as you feel (PAYF) cafes. We need to log the data from each of ~100 cafes, generating perhaps 50 rows each par day. I estimate that this could (if we expand) generate around 1 million rows per year. How best to store this data in a system that allows computer noobs to log their data in a way that is as easy and user-friendly as possible? That's the essence of my question. Details below.
Introduction to data storage
I know that the appropriate way to store data is described by the term 'tidy data' [@Wickham2014]. The rules of data stored in this way are:
Each variable forms a column.
Each observation forms a row.
Each type of observational unit forms a table.
The data collected by the TRJFP operates at several levels, so several tables are needed. These are
- Food items intercepted
- Daily totals (e.g. meals served, finances)
- Interception points
The first 2 tables are usually collected daily by each cafe. The 3rd table on 'interception points' (where food is picked-up from) is also collected by each cafe but is only updated when a new interception point is logged. The 5th table provides a global overview of all the cafes and is updated every time a new cafe is added.
By far the largest table in the database will be 1,, which could store perhaps 50 new items per day. Assuming (optimistically) that in the future cafes are dilligent in logging the data, we could log data from 100 cafes. That would be 5,000 rows per day or around 1.8 million rows of data per year. This is not really Big Data, but it is worth thinking carefully about how it is stored.
The variables associated with each table are described below. Critical to the useability of the data is that the datasets can be linked. Therefore linking variables connecting each table are described.
There are 3 main options for storing these tables, from simple to complex.
- On Google Fusion Tables
- As regularly backed-up .csv files managed through an online server system like Shiny
- On a fully fledged database system, like postgres