Case study Continued

Here is the spreadsheet we created from John's receipts.

To understand data better it can be useful to have a real good look at it and answer a number of questions. I won't be listing all these questions here but they are basically about how bits of information relation to other bits. If I change that bit, will this bit change?

As well as understanding our data better, this process will help us make an efficient database that stores the data in such a way that its easy to add, change and report on the data. A database can then be made - MS Access is a tool designed to do just that, but Excel can be used too. Looking further there are many other database tools we can use that need more investment.

  • Look for items of data that repeat - For example, the name of the stores. Recording the name of the store is ineffiecent and, if the store changed its name we would have to change the name in lots of places.
  • Look for items that depend on each other. For example, the address depends on the store, and the staff member will most likely only work for one store.
  • Look for data that can be calculated from other items.
  • There is a technical process to work through on each item. For this example I would arrange the data in 'tables' as follows.

You will notice that I've added some extra items not on the spreadsheet, for example, StoreId. We need this to identify the stores in the different places so that should a store change its name, we only have to change this in one place.

Data Analysis is not always an exact science, and other factors might come into play. For example, I've assumed here that all the stores sell wallpaper in 10m rolls. We might find that isn't the case, in which case the item 'Sell Unit' would be mved to the 'Prices' table.