Thursday 12 January 2012

How do you keep track of all your seeds?

During the lunch with Patrik, we discussed how to organize and keep track of all the seeds. Usually, there is no problem at all from the beginning. Then you start swapping seeds with other chileheads, and the collection keeps growing. And all of a sudden you pass some kind of critical point, where you no longer know what varieties you have, or from whom you got them, and so on.

At least that is what happened to me some years ago. I guess I was driven by a desire to have more than 1000 different varieties. Jukka and I was racing towards that goal, and I think we reached it more or less at the same time.

The Solution

So, with hundreds of varieties, how do you keep track of them? Being an IT Architect, I'm interested in solutions built on a flexible structure. At the same time, I didn't want a complex system, so I decided to build it on OpenOffice, a free office suite that runs on several operating systems. Today, I use LibreOffice (http://www.libreoffice.org/) but it is built on the same code base.

Basically, I have two functions.
  1. The inventory. All the seeds I have (and also the ones that I don't have anymore).
  2. The yearly log. All the varieties that I grow each year.

The rest of this article assumes that you have some knowledge of relational databases. It is a short walk-through of the solution. If you are not familiar with LibreOffice, it is quite easy to get started with.

Or, maybe you just want to download the database file, and try it out for yourself. You find a link to the file at the bottom of this post.


Tables
To make it as simple as possible, I created two database tables. LibreOffice contains HSQL (a datababase engine), so all the functionality is contained from the beginning.


1) The varieties table. Contains all the variety information. Name, species, an approximation how many seeds I have, date when I got the seeds, from whom, and so on.
2) The years table, which contains information about sow date, when the seedlings germinated, when I repotted, if I have taken a picture of the flower, pod etc.


Varieties
To give you an example. This is how the varieties table look. You see name, amount, genus, species, and so on. At the end you see "availableforswap", which was a way for me to flag a variety as private because of some reasons.

To add a variety, you simply add a row at the end of the dialogue above. Like an ordinary spreadsheet.

Years
The years table is more complex, since it builds on the variety table. Remember the purpose of this table is  to keep track of what you are growing this year. And in order to grow something, you have to start with a seed - which is in the varieties table. So it's actually quite logical. Therefore, you have to know the " variety id" of the variety in the varieties table. Then you add the year, and the date you start the seeds. I am not using all the fields shown below. Sometimes I add fields, and sometimes I don't use them. It's all depending on the level of my current ambitions :-)


Queries
Tables are the place where the information is stored. Then you need some queries with logic to reach that information. For simplicity, I have two queries.

1) Available. Shows all the seed varieties that I have. In other words, where the amount of seeds are more than 0.
2) Chiles2012 (in this case). Extracts all the current chiles that I grow during a year.

Below you see the Queries section.



The available query
LibreOffice has a built-in editor for queries, and this is how it looks. You pick the fields from the chosen tables, and decide if they should be visible or not. Here you can see the amount column, where I have a Criterion which says > 0. Yes, I want to see all the varieties that I have more than zero seeds of.


This query can then be connected to an ordinary spreadsheet, and this is how it looks.

The yearly query looks like this. Here you see that the variety table and years table are connected, and there is a line between ID and varietyid. That is what I mentioned earlier. You have to have a seed in order to grow the variety. (Well, you could of course have received the plant, and that is something this solution will not handle...)


When I run the chiles2012 query, this is what I get after I have exported it to the spreadsheet.




Here is the file -> http://petterssononline.com/chiledb/chiledb.odb
You will need OpenOffice or LibreOffice in order to play with it. And you will also need to populate the tables with your data. So the file is empty when it comes to data.

Well, that is about it. I hope that I have inspired some of you to play with LibreOffice in order to keep track of your seeds. Anyway, I'm interested in hearing how you keep track of your seeds. Please comment.

Hot regards,
  Mats


3 comments:

  1. Wow, Mats! Det där var verkligen imponerande - både antalet chilefrön och systemet! Synd att man inte är IT-utbildad (det önskar jag ofta att jag var)! Men jag har gjort ett liknande system som jag har fört de senaste 12-13 åren i Excel. Men då gör jag ett nytt dokument för varje år, så jag ser ju inte alla år samtidigt. Ska kolla igenom ditt mer och se om jag skulle kunna utveckla det, för ett totalt register är ju ovärderligt. Särskilt om man kan koppla dem! Sedan kommer ju nästa fråga - hur man förvarar själva fröpåsarna? Där är jag lätt nitisk och har det mesta (som är katalogiserat och sorterat) samlade i gamla diskett-lådor, eller mindre utdragsbyråer med mellanliggande flikar i bokstavsordning, men både Excel-systemet och det fysiska systemet är uppdelade på samma sätt - de är indelade efter perenna växter, bienna växter och anueller. Därifrån har jag sedan brutit ut exoter och grönsaker. De har sedan indelningar efter de vetenskapliga namnen. Jag har absolut inte så många chilesorter som dig, men det är trångt i en dubbel gammal diskett låda att få plats med dem i vart fall! :) Ha det bra - och tack för tipset! :) Mia

    ReplyDelete
  2. Hej Mia!
    Nu för tiden har jag inte så många fröer, men det är ju kul att samtidigt kunna få en återblick på vilka sorter man haft.

    Du har rätt. En sak är ju hur man håller reda på sorterna, en helt annan utmaning är själva förvaringen. Ett tag hade jag tre ganska rejäla lådor. Nu får jag ledigt plats i en. Men, pendeln svänger, och rätt vad det är så har man väl samlat på sig en massa igen :-)

    Trevlig helg!
    Mats

    ReplyDelete
  3. Härligt, de är med sånna som er som man vill byta fröer med. Dvs ni som har full koll. Jag hade ju en lista för ett par år sedan. Men sen blev det så mycket fröer att jag gav upp. Nu har jag bara tre pärmar med fotofack. En extra tjock annuum pärm. En baccatum pärm och en chinense + övriga. Väldigt trevligt att bläddra i. I början la jag ner en bild i varje fotofack. I alla första början gjorde jag också egna fröpåsar med foto och text - snyggt men väldigt tidskrävande.

    ReplyDelete