Database?

[Kelly]

Hey all -

I'm hoping to get some opinions here. So far I've been keeping all my information on seeds for my seed bank in a spreadsheet. The information on each variety includes accession #, species, variety, common name, history, grow out years, etc.

As my seed bank is growing, the spreadsheet is getting rather unwieldy and doesn't do the job too well anymore. It's not easy to search things up, or create a new entry (because I have to search through and see what my last accession number is).

I'm thinking it's time to build a proper database, but I must admit I'm hopelessly clueless when it comes to databases.

I attempted using the Open Office database, but I found it clunky. I also found it frustrating to read, making it difficult to look up information.

I know a bunch of you either have banks, or large quantity of seed, so I'm hoping somebody here can offer a little guidance on what is the easiest database-type software you use?

Any information or suggestions is more then welcome, I need to get this under control before the seed bank grows much bigger!

Thanks!

[Joseph Lofthouse]

I use a system akin to a card catalog: Put the seeds in an envelope. Throw all the same species in a box. Giggles.

I was the database administrator for a chemical research laboratory for years, and have never even thought about putting my seeds into a database...

In general the trouble that non-programmers get into when they attempt to convert a spreadsheet into a database, is that they just import the "flat-file" data from the spreadsheet. That adds search and matching capabilities that the spreadsheet doesn't have, but the true power and usefulness of a database comes by "normalizing" the data.

What normalization would mean in a seed bank application would be to store like information together.... So for example you would make one table with information only about the species... An ID number, its common name, it's latin name, etc... Then you would make one table only about dated mechanical things: Like planting date, germination date, harvest date, daily observations, etc... Perhaps a table to track genealogy. And a table to track varieties (sorry I don't know what an accession number is). And then there would be the relational tables: What Accession Number matches what species, etc...

I think the open office database would do great for a seed tracking application.

Regards,
Joseph

[Joseph Lofthouse]

Jun 27, 2010 15:33:27 GMT -5 Joseph Lofthouse said:

What normalization would mean in a seed bank application would be to store like information together.... So for example you would make one table with information only about the species... An ID number, its common name, it's latin name, etc...

Just to show what a bugger normalization can be, my suggestion breaks the normalization rules because a plant might have multiple latin names and common names and my first assumption was that they would only have one of each.

Regards,
Joseph

[Joseph Lofthouse]

So I spent a few hours tonight thinking about seeds and how I would keep track of them...

I'd key the database off of my own personal accession number... I'd assign my own number to each set of seeds as it arrived or as I collected them from a breeding project.

I'd make a relational table to keep track of the seeds pedigree. In the case of a mass cross, it would list the accession number of each parent referenced to the accession number of the children. In the case of growing out an accession it would just reference the ID of the children to the ID of the parent.

Then there would be a table to record the location of each planting of seed. It would be connected to a one-to-many relational table keeping track of dated events like planting, germination, harvest, etc... This would be keyed to a patch of corn planted on the same day, etc... and a patch of the same seed planted on a different day would be a different location.

Send me a personal message if you'd like a copy of my draft open office database.

[spacecase0]

I have also been thinking about how to keep all the seeds organized,
I am not even sure I could use a system like joseph is talking about.

I have been making a separate page for each seed jar with what is in it,
with notes as to what is going on with them,
when I get new seeds from anywhere, I make a new page for them.

I just keep the pages in the same order as when I got the seeds,
it seems to work ok,

not sure a system like that would move easy into a computer...
the fields are standardized on what I have written down,

how do big seed banks do it ?

[Kelly]

Joseph -

WOW, thanks for all that information! My non-database savvy mind is still trying to process it all! My problem with Open Office Database is that I cannot, for the life of me, figure out how to be able to say, looking up varieties according to an ID, or if I wanted to look up varieties and sort them by when their last grow-out year was. And then the problem is I can't seem to figure out how to view a report without that report cutting off a bunch of information (it seems as simple as word wrapping columns in the report, but I don't see how there is any way to do something that simple!).

Spacecase0 - I imagine big seed banks have their own databases that programmers have set up for them. Unfortunately, I don't know any database programmers (or database programmers who would be kind enough to code something, from scratch, for free). I think it's a pretty big job doing something like that.

[Kelly]

Update:

Been fiddling around more with open office base, and it actually seems to be going pretty well. I'm not sure why I was having so many problem with it earlier when I was trying to configure it to my needs, but now it seems to be going well. Now it's just a matter of transferring all the data. Which is taking longer then I thought. I wouldn't have waited so long to get a proper database going!

[Joseph Lofthouse]

OK then. I've posted a draft of what I think a seed database might look like.

garden.lofthouse.com/db/

Very rough draft, and I've never programmed in OpenOffice before, but
it's a start.

Regards,
Joseph

[grunt]

Kelly: For word wrapping go to Format = Cells = Alignment, and check the box for wrap text automatically

[Kelly]

Joseph -

That is amazing! This is going to sound totally idiotic I'm sure, but what exactly is the purpose of the forms? As opposed to the table or the reports I mean? Is it simply a different way of viewing information? Remember, I did say I was hopelessly knowledgeable about databases .

Dan - That's what's so confusing me, in the tables within the database there's no Format -> Cells -> Alignment. The formatting box that I have is greyed out for some reason. But I did discover how to get the reports to look proper, so at least all the info is viewable by a click of the button.

[Joseph Lofthouse]

Jul 3, 2010 10:36:17 GMT -5 Kelly said:

Joseph -

That is amazing! This is going to sound totally idiotic I'm sure, but what exactly is the purpose of the forms? As opposed to the table or the reports I mean? Is it simply a different way of viewing information? Remember, I did say I was hopelessly knowledgeable about databases .

Dan - That's what's so confusing me, in the tables within the database there's no Format -> Cells -> Alignment. The formatting box that I have is greyed out for some reason. But I did discover how to get the reports to look proper, so at least all the info is viewable by a click of the button.

The forms are the "graphical user interface" to the raw data that is stored in the tables... The forms are automatically keeping track of relationships, and make it possible to work with names instead of with ID numbers. (The forms are displaying information from many tables simultaneously.) It seems like the forms could also be used for selecting data, but I have never used OpenOffice database before this week so I'm swimming up the falls. If I was familiar with OpenOffice the forms would have drop-down boxes on them for selecting, sorting, and entering data. (Such as all accessions grown this year.)

Regards,
Joseph

[Joseph Lofthouse]

Found and fixed a bug. Uploaded new file.

garden.lofthouse.com/db/

Regards,
Joseph

[spacecase0]

thank you for creating and posting this,
I like it.
now I have some data entry to be doing