Data Management Overview

 

This section describes the logic and content of the relational database we developed to organise and present the data we collect in the vineyard, winery and cellars. In the beginning, we recorded our observations in a word document. In 2012 we got more serious about tracking data and started using spreadsheets in Excel. As the spreadsheets got more complex and data became harder to compare across vintages, we decided in 2017 to upgrade to a relational database software called FileMaker. Note, I delved into this venture as a dilletant (like the rest of this venture), without prior knowledge of relational database design. The design of this database reflects this; a professional would have easily come up with a more efficient structure. The advantage of the do-it-yourself approach is that we can amend it on the fly and we do so continuously.

 

Concepts

The following graphic illustrates the thinking behind the organisation of our data collection:

  • The second column identifies the key activities (starting with Vineyard Management and ending with Bottle Aging in the cellar) and the transitional activities which bridge the locations from vineyard to winery (harvest), winery to barrel cellar (barrelling) and barrel cellar to bottle cellar (assemblage).
  • The third column describes how we track activities in these steps by different management units. In the vineyard, we track activities by Vineyard Block. In the winery, we track activities by Fermentation Batch. In the Cellar, we track activities by Cellar Batch (e.g. barrel). In the Bottle Cellar, we track measurements by bottles. This implies that we need to define the transformations of management units in each year: which vineyard blocks are assigned to which fermentation tank, which fermentation tanks are used to fill barrels in each Cellar Batch and which Cellar Batches are used to create specific Bottle Batches?
OverviewGraph.png

 

  • The fifth column identifies the key activities that we track and the measurements that we take in each process step.

This may all sound too theoretical and abstract at first and is only made comprehensible by describing what date we collect in each process step and how we transform that data from one step to the next. Ultimately we want the understand which vineyard block contributed what volume of grapes to each type of bottle in each vintage, how these grapes were treated through fermentation and cellaring and how can we compare our activities and results across all vintages.

 

Relational Databases

To describe the database we need to define some terms.

We organise our data in Tables. A table has a name and contains a collection of Fields. Each field represents a specific piece of data which can be a number, a date or a string of text. A table has many Records. Each record contains a set of data. To illustrate, think of spreadsheet listing your inventory of wine bottles. Each spreadsheet column represents a field (BottleID, Bottle Name, Vintage, Appellation, Price Paid, Store where Purchased, etc.) and each row represents a record (e.g. row 17 would show: 17, Veuve Cliquot, 2015, Champagne, $40, K&L, ….)

The beauty of a relational database is its ability to define Relationships between tables. To illustrate, think of a second spreadsheet listing your tastings with the columns: BottleID, Date, TastingNote.  Row 10 of this spreadsheet records a tasting (e.g. 17, Jan-4-2018, “Good”). In a relational database, you define a relationship between the inventory table and the tasting table by “linking” the field BottleID in the inventory table to the field BottleID in the tasting table. This gives you the ability to create interesting reports; e.g. listing of bottles that tasted “good” by the price paid.

To put actual data into the database and to review data, we use Layouts. Layouts define the arrangements of multiple fields, lists and graphs on a computer screen. Each layout allows the user to scroll through the records of a given table and display the contents of a given field in that record and, importantly, records in related tables.

 

Continuous Improvement

The principal benefit of developing our home-made relational database system is flexibility. We could have licensed a commercial product, but it would have forced us into a fixed schema. We continually change the tables, relationships and layouts as we change the way we manage the vineyard, make the wine and manage the cellar.

When we started this effort, we had no clue how complex the database would become. The diversity and amount of data required to begin describing a vineyard and its annual maintenance, the annual winemaking and the multiyear cellaring of wine is an overwhelming challenge. So is reading through these pages of the website. To whet your appetite, you may want to skip forward to the pages which describe the INPUT layouts and, even more interesting, the REVIEW layouts. They best illustrate the power and utility of the database.

 

Tables & Layouts

The following pages describe how we define and organise the data.

First, we present Tables in Layouts which contain all records of the respective table and key records of related tables. The names of these layouts are “ALL: XX” where XX stands for the name of the table. We show a page each on :

  • Tables for Vineyard activities
  • Tables for Winemaking
  • Tables for Cellaring

We use the “ALL:XX” layouts to create new records and input data in instances where no special layout is required for these actions. Some of these “ALL: XX” – layouts also contain tabs which summarise the data across all records.

Following that, we show how we create new records in a table and how we input data into these records when specially designed layouts are more convenient for these actions. We present a page each on :

  • Layouts to create new records in tables; “CREATE: XX”
  • Layouts to input data; “INPUT: XX”

Finally, we describe layouts which are specifically designed to review data; they are named “REVIEW: XX”

Note, in the screenshots which follow to illustrate each layout. Generally, the white fields represent data that needs to be input, the pink field represent data that is calculated from the white fields, and the yellow fields are pulled from related tables and shown to provide the context for the respective table. We show each layout in two screenshots, one which the names of the respective fields, the other shows a sample data-set.

 

A pdf file of the Data Management section can be found HERE

 

Previous page: Home
Top of page: Go
Next Page: Tables for Vineyard Data
Last updated: August 4, 2018