Garden Tracking Database using PostgreSQL

This year my wife and I have taken our gardening to a whole new level. We tore down a nasty old, wasp-infested shed in the sunniest part of our yard and built a raised garden bed in its place. We also setup an area inside with some crazy (awesome) LED grow lights that allow us to start plants from seed and produce healthy transplants, all in our house with zero south facing windows.

If you're not interested in the database I'm building, feel free to just look at the pictures of our garden scattered throughout this post!

This post is not about our gardens. It's about the database I am building to track our gardening endeavors though there are a bunch of pictures of our garden throughout.

Our new raised garden bed and some other various containers.

Quick Overview

My gardening database is built in PostgreSQL 9.3 and I have it running on a virtual machine here in my local environment. The code I provide in this post should be fairly straight-forward to adopt to any major RDMS (SQL Server, MySQL, etc) but it probably won't work without at least a little modification. If you need help migrating code or adopting to your needs, please contact us, that is beyond the scope of this post.

Warning: The code you are about to see is in its very early stages, use at your own risk.

Our Gardening Setup

The more our garden has grown, the more I wanted to be able to track how it's going. It's hard to answer the questions of "When can I plant plant X?" living at 6,000 feet. We have a fairly short growing season; it has snowed on Mother's day two years in a row now, and it's not unusual to have at least one light snowfall around Halloween.

Throw in having an indoor growing area spoiling seedlings with almost 14 hours of light each day, plus a protected raised garden bed and I'm just not sure what my growing season looks like. I want to make the most of it, so naturally I figured the best thing to do was to gather some data (surprise!) and that sounds like the job for a Database.

The LED grow lights use a mix of red and blue LEDs to produce a pink or purple light that is really tough to look at... but does wonders for the plants!

We plant seeds in soil in little cups that we place on a tray below the seedlings for propagation. Once seedlings start to emerge the individual cups are moved under the LED grow lights to grow until they're ready to transplant and the weather permits. The transplants are then either put in the raised garden bed, the ground, a container, or given away to friends.

An Anaheim pepper plant in transit to its new home.

Data to Capture

We want to be able to track the details from planting seeds through the harvest. Since it's only June I'm certain this design will evolve and improve throughout this fall and upcoming spring as I better define the details I want to track. The basic life-cycle I want details on is:

seed -> individual plantings -> plants -> harvest

Seed Details

I decided to group seeds into broad categories that make sense for me and our garden using a table named public.seed_group. It's basic for now but may be expanded in the future.

CREATE TABLE public.seed_group 
(
    id  SERIAL NOT NULL PRIMARY KEY,
    name    VARCHAR(200) NOT NULL
);
CREATE UNIQUE INDEX UQ_seed_group_name ON public.seed_group (name);

Now insert a few default values:

INSERT INTO public.seed_group (name) 
    VALUES ('Tomato'),
        ('Herb'),
        ('Pepper'),
        ('Flower'),
        ('Greens'),
        ('Gourd')
;

Now to track the actual seeds. Notice the foreign key linking back to the public.seed_group table. This will make it easier later on when I want to compare various plantings of all the pepper plants we've planted this year... a total of 3 varieties from 4 different sets of seeds!

CREATE TABLE public.seed
(
    id  SERIAL NOT NULL PRIMARY KEY,
    seed_group_id   INT NOT NULL,
    name    VARCHAR(200) NOT NULL,
    days_sprout SMALLINT NULL,
    days_mature SMALLINT NULL,
    seed_year   CHAR(4) NOT NULL,
    notes   VARCHAR(2000) NULL,
    CONSTRAINT FK_seed_group FOREIGN KEY (seed_group_id) REFERENCES public.seed_group (id)
);
CREATE INDEX IX_seed_name ON public.seed (name);

Most of the details I'm tracking on seeds are typically available when purchasing commercial seeds, such as from Burpee. I've included the number of days to sprout (days_sprout), number of days until maturity (days_mature) and the year the seeds are from (seed_year). Not all of those values are always given or known so I opted to allow NULL values.

Planting

The next stage is to track the seeds to individual plantings of seeds. I'm using a table named public.planting to capture the date we planted the seeds, how many seeds were planted and how many sprouts come up. This table has a few CHECK constraints on it to help ensure data integrity, such as the planting date must be before the date sprouts came up (CHECK (first_sprout > planting_date)). It also contains a foreign key to link plantings back to the seeds that were planted.

CREATE TABLE public.planting
(
    id  SERIAL  NOT NULL PRIMARY KEY,
    seed_id     INT NOT NULL,
    planting_date   DATE NOT NULL,
    num_seeds_planted   SMALLINT NOT NULL CHECK (num_seeds_planted > 0),
    first_sprout    DATE NULL CHECK (first_sprout > planting_date),
    num_sprouts SMALLINT NULL CHECK (num_sprouts <= num_seeds_planted),
    notes   VARCHAR(2000) NULL,
    CONSTRAINT FK_seed_id FOREIGN KEY (seed_id) REFERENCES public.seed (id)
);

Plants

Some time after planting, sprouts will come up and grow up to be transplants and I want to track that relationship. I want to see how plants perform based on planting date, transplant date, where they were planted, and so on. The public.plant table has a few BOOLEAN (T/F) columns, a few date columns and a place to put general notes. There is a foreign key to link plants back to the planting cohort they originated from.

CREATE TABLE public.plant
(
    id  SERIAL  NOT NULL PRIMARY KEY,
    planting_id     INT NULL,
    transplant_date DATE  NULL,
    gift_date DATE NULL,
    container BOOLEAN NOT NULL,
    raised_bed BOOLEAN NOT NULL,
    ground BOOLEAN NOT NULL,
    gift BOOLEAN NOT NULL DEFAULT False,
    first_harvest DATE NULL,
    last_harvest DATE NULL,
    notes   VARCHAR(2000) NULL,
    CONSTRAINT FK_planting_id FOREIGN KEY (planting_id) REFERENCES public.planting (id)
);

The first pepper that formed on our indoor Jalapeno plant planted in early February.

Progress Check

Let's revisit the original life-cycle that I wanted to track, which we're capturing at least 75% of now.

seed -> individual plantings -> plants -> harvest

Now let's look at how the foreign keys in the tables point backwards through that chain:

plant.planting_id -> planting.id  (plant -> planting)
planting.seed_id -> seed.id (planting -> seed)
seed.seed_group_id -> seed_group.id (summarization of seeds)

Hopefully this helps show how a relational data structure can model real world processes. Thinking about these relationships helps plan out the details before starting and will drive the database design process in the right direction.

Harvest

You may have noticed that I haven't talked about the harvest data yet. I don't have enough details about what I want to track yet so I haven't built it. My experience shows that if you build functionality off half of an idea you get about a quarter of the design right. You may have noticed the public.plant table has fields for first and last harvest dates, but that's as far as I took it for now.

What's next

Other than the omission of harvest data, the biggest shortcoming so far is a functional user interface (UI). At this time I'm handling all of the data modifications directly against the database using PgAdminIII, but that is getting rather tedious even for me. Now that the main database design is in place I plan on moving forward with making it easy to work with.

Working out how I want to generally query and report on the data for my own purposes is another task ahead. That should naturally evolve over the summer as we look forward to doing a late summer planting on to next spring.

Stay tuned for more progress on our garden tracking database!