Question How to design a plant database (1 Viewer)

hockey8837

Registered User.
Local time
Today, 18:02
Joined
Sep 16, 2009
Messages
106
Hello,
I'm currently creating a database in which I can enter information about plants and track not only species information but also planting requirements & growing conditions, bloom colors, pollinator information, and bloom time of year.

My question is this: the most important type of report I want to get out of this DB revolves around bloom time. I want to be able to show a month-to-month timeline showing which flowers/plants/trees will be in bloom; almost like a horizontal bar graph, a large list showing all flowers in db, & when the start and end blooming.

I started by just having a check box for each month, but I then realized I don't know how to tabulate a report to get the flowers to be organized in ascending order by bloom start period, rather than in ABC order. Now, instead of 12 check boxes, I have a 'bloom start' text box and a 'bloom end' text box. I don't need the date picker because I really just need month, I don't need more details than that. However, I'm still having the same report issue of getting them in ascending order according to month.

Any suggestions?
 

Steve R.

Retired
Local time
Today, 18:02
Joined
Jul 5, 2006
Messages
4,617
In a sense you are not really using date values. What I would suggest, as part, of your approach a table with two fields one an integer value and the other the name of the month.

You will use this table as the record source for a list box of combo box for entering your month. The combo box (list box) will display the month but the value stored in your main table will be the integer. You can then sort the "months" in your main table by the integer value.

A minor issue to consider. What happens if your plant (however improbable) starts to bloom in November (11) and ends its blooming period in February (2)? I would suspect that most plants would bloom lets say in March (3) and end by June (6) which would allow you to easily figure out the blooming window.
 

hockey8837

Registered User.
Local time
Today, 18:02
Joined
Sep 16, 2009
Messages
106
Hello,
Thanks for the advice. That is kind of what I'd started developing, I just was not sure whether that was the best way to go about this or whether it was round-about.
Your point about plants in bloom from fall through the calendar year is a good one; we do plant items that are grown for their 'winter interest,' though this may not necessarily mean it's in bloom, it may still be something we want to track. I guess there is probably no way to tell the DB to go back around again, unless I were to assign additional months that are only applicable for plants that continue to have interest through the winter; i.e. January (1), February (2), March (3)...November (11), December (12), January-A (13), February-A (14), March-A (15).

I don't know if there is another way to do this that would be better. Thanks for the input!
 

Access_guy49

Registered User.
Local time
Today, 18:02
Joined
Sep 7, 2007
Messages
462
before you dump too much more time into this, you may want to look at BG-Base.
It is a pre-established software industry known and used in some of the largest Botanical Gardens in the world. (I used it for my thesis).

Otherwise it looks like your going to have a problem when you get into plants that bloom for short periods or mid way through months. personally I would go with a "Bloom start" and "Bloom end" date fields. As you monitor bloom dates you can get much more specific and after years of data collection may be able to see shifts in bloom times over some species.

The problem with using integers in my opinion is that you loose out to some degree on a valuable detailed data type. alot can happen with a plant over a 2 week period.
 

Steve R.

Retired
Local time
Today, 18:02
Joined
Jul 5, 2006
Messages
4,617
Your point about plants in bloom from fall through the calendar year is a good one; we do plant items that are grown for their 'winter interest,' though this may not necessarily mean it's in bloom, it may still be something we want to track. I guess there is probably no way to tell the DB to go back around again, ...

This can be resolved through the use of of the IIF function.

May (5) - March (3) = 2 (March, April, May which equals 3)

February (2) - November (11) = -9 (November, December, January, February which equals 4)

Code:
BloomPeriod=IFF(EndMonth - StartMonth +1>= 0, EndMonth-StartMonth+1, EndMonth-StartMonth + 13
 

hockey8837

Registered User.
Local time
Today, 18:02
Joined
Sep 16, 2009
Messages
106
Re: How to design a plant database- reports & calendars

Thanks for the suggestions. I'll check them out to see how it goes...But first...

I'm now trying to create a report which will show me a cross-section/bar graph type calendar displaying all species. I need the plant's bar to start in the associated month [Start Month] and end with [End Month].

I found this Reports Calendar Demo DB online somewhere (can't remember where, now), which I've attached. I'd like it to look something like the crew rotation schedule; I've actually gone in and added to my Months calendar which previously had 2 columns only for Month and Month # (like January, 1) and added a 3rd date "1/1/01", etc. for all months to help function with this report (year is inconsequential for me but that's what it is in the demo).

However, since I only have a species bloom start & end dates and nothing to compare it to as in this demo report (ship captain's start & end time on board vs the ship they're captain of), I'm kind of stuck again.

I'm having a hard time finding anything online about reports calendars in general. Any suggestions?
 

Attachments

  • CalendarReports.zip
    156.7 KB · Views: 631

gardengal444

Registered User.
Local time
Today, 18:02
Joined
Feb 2, 2010
Messages
18
I have been working on a similar issue with tracking bloom periods. In the past i have found it difficult to successfully record start/end dates for a very large garden area. So instead I have a series of "observation dates" and "Bloom percentage". I'm sure this could work out with start/end dates too. I converted the date to a week of the month and produced this. Its in excel right now, but I am planning on getting it into Access somehow.

 

DCrake

Remembered
Local time
Today, 22:02
Joined
Jun 8, 2005
Messages
8,632
There is no reason why you cannot store the base information in Access and get it to fire the data out to Excel to provide the layout you have displayed above.

David
 

hockey8837

Registered User.
Local time
Today, 18:02
Joined
Sep 16, 2009
Messages
106
Hi There,
I actually was able to solve this problem with the help of others on a different thread. The person who helped me (who I still can't thank enough) wrote a module for my DB which took the bloom start and end dates, and attributed a # to each month it bloomed; i.e. June-Aug bloom=6 7 8.

This way, I can run a parameter query for either 1 month (by entering the same # in both prompts) or any combination to see what will be blooming during this time.

I've attached my DB, feel free to use this as your starting point. I had to delete my photos from it in order for it to be small enough to attach. Your idea of % of bloom time is pretty innovative, though. This may be a great addition to my DB-though, the way mine functions now pretty much meets my needs for the most part. Yours may be more specific.

Enjoy!
 

Attachments

  • flower database2.zip
    127.4 KB · Views: 1,448

gardengal444

Registered User.
Local time
Today, 18:02
Joined
Feb 2, 2010
Messages
18
thanks for the innovative complement. I wish I had known how it would end up when I actually started tracking the data in the garden throughout the season. In reality, I recorded 1,2,3,4 or 5 where 3 was peak bloom and 1,2 were winding up and 4 and 5 were winding down. When I could not come up with a way to represent that graphically, I converted my 1:5 values to estimated bloom percentages which yielded the above. In the past when I attempted to record start/end dates, I noted that the plants don't really respond well to that "bucket". (nor do I walking around a garden with 500-1000 plants!) A true "end" is not so definitive for some plants. Some bloom in a small way for a long time.

Thanks for sharing the flowers database. I will download it and explore it as I start my Access database. I am still in the planning stages. One thing I have seen so far in my design plan is that plants and gardens yield a ton of data. I am anxious to see what you have captured. My potential table list is growing every time I look out the window :)
 

hockey8837

Registered User.
Local time
Today, 18:02
Joined
Sep 16, 2009
Messages
106
Yes, I'm still playing around with what I want to track-because the possibilities are endless! For my purposes, at this point, bloom start and end will do. But, you're right, it may be useful down the road to have that wax and wane tracked, too. I've found that most of the data I've found about the plants is not specific enough to really be able to reliably track this across the board. Many sources simply list season for bloom periods, even rather than months. This is why I chose months over weeks for bloom calculations (for now), though weeks would be more accurate. I think I will go back in later and add areas which also track fall or winter 'interest', since, though some things may not have flowers on them, they're still attractive for other reasons.

Good luck! I'd be interested to see what you end up with down the road!
 

Steve R.

Retired
Local time
Today, 18:02
Joined
Jul 5, 2006
Messages
4,617
One thing I have seen so far in my design plan is that plants and gardens yield a ton of data. I am anxious to see what you have captured. My potential table list is growing every time I look out the window :)
The weather here on the east coast of the United States has been pretty chilly. We haven't got our heating bill yet, can't wait to open it! But, in terms of your garden and your database design - do you collect/record heating/cooling days? I assume that knowing whether the temperature is hotter or cooler than normal would have a predictive value for when plants will bloom.

A word of caution though, there is such a thing as collecting too much data.
 

gardengal444

Registered User.
Local time
Today, 18:02
Joined
Feb 2, 2010
Messages
18
Steve, OMG! yes heating/cooling days does get into the too-much arena for me. However, having said that, I do usually mark in my journal the day I see my first flower (Iris reticulata) each March just to set the stage for the predictive value for the rest of the season!!
 

gardengal444

Registered User.
Local time
Today, 18:02
Joined
Feb 2, 2010
Messages
18
this conversation prompted me to get back into my plant db design. I am an experienced software person, but a novice in DBs so I ask your help in steering me in the right direction.

I've attached a prelim list of my proposed tables and columns. I haven't addressed foreign keys yet but it basically shows the info I want to capture. The right side is instance data, the left is more like constant data (not sure of correct DB terminology). I think the table organization is pretty straightforward, except for the most important part.

I have not figured out for sure, but I suspect it is so, that each entry in the Plants table should be an instance of a particular plant in a particular location. So if I have the identical plant in two different garden beds, I will have two Plant entries for that row, distinguished by its location.

The part that bothers me is that the genus & species dictates the contents of many of the other fields so Plant P in Location L will have a lot of overlap with Plant PP in Location LL. And I know that's wrong.

Maybe the Plant table should just be a "generic" plant? and Cultivar and Location (among other things) differentiate the "instance".

I also get dizzy when I think about PurchaseInfo. I could easily acquire two plants at the same time and put one in one location and the other in another location. So in my first model that I proposed I would have to repeat the same PurchaseInfo for two plant entries.

I know this decision is important to a good working DB design, so I thought I might run it by the experts. I have to extract something out, but not quite clear best approach.
 

Attachments

  • DesignNotes.pdf
    44.5 KB · Views: 847

Steve R.

Retired
Local time
Today, 18:02
Joined
Jul 5, 2006
Messages
4,617
I would recommend reading up on normalization.

Concerning location, do you have a grid established? I think it would be easier to locate the plants that way. But then again, I don't know how big the parcel is or how hard it is to find stuff.

Concerning light, category, moisture, nutrients, and possibly special attributes: These could be drop down lists, assuming that you are using "standard" definitions. The drop down list would use tables of two columns, one with a number and the other the text. But you would only be using the number.

Concerning Purchaseinfo, this would be a separate table, each purchase action would need its own serial number. The vendor would be a separate table used as a drop down list. Too complicated to explain further. You may want to get a book like: "Access Database Design and Programming" by Steven Roman. The book, I see is getting a bit "old", published in 2002.

I assume that you are selling plants to vendors? If that is the case you may want to have an "expiration" date for each blooming plant. I assume that you only have a limited window in which to sell plants, so based on a plant's bloom date; how many days to you have to sell your inventory of that plant?

Just for kicks, I took a quick loot at some personal automated weather stations, just think of the fun you could have with a data feed directly into Access! Data overload galore. :D:D:D
 

gardengal444

Registered User.
Local time
Today, 18:02
Joined
Feb 2, 2010
Messages
18
Thanks on the normalization pointer. Yes, I think that is the crux of my question. I didn't quite follow all the subtleties of the conversation, but that is the essence of my query. (no pun intended!)

I think I need to stop and think hard about my data and my Use cases and my one-to-many, one-to-one, many-to-many(??) relationships. The attribute lists seem pretty easy. Its the main entity and how to represent its multiplicity and overlap that gets me confused.

I do have an OReilly book for Access 2007. I have read a little, but I have a hard time focusing on reading. I just want to dig in and play! But, I think you are right. I should slow down and RTM. I learn best by seeing real examples, so I think a book is a good thing. My intent for now was to start out with a small prototype in the "dig-right-in" style, but maybe too premature for that.

Nope, I do not sell plants. This is to manage my own plant collection (500-1000 or so plants) and as a learning exercise to expand my repertoire.
 

grotman

New member
Local time
Tomorrow, 00:02
Joined
Jul 1, 2021
Messages
1
Hello,
Thanks for the advice. That is kind of what I'd started developing, I just was not sure whether that was the best way to go about this or whether it was round-about.
Your point about plants in bloom from fall through the calendar year is a good one; we do plant items that are grown for their 'winter interest,' though this may not necessarily mean it's in bloom, it may still be something we want to track. I guess there is probably no way to tell the DB to go back around again, unless I were to assign additional months that are only applicable for plants that continue to have interest through the winter; i.e. January (1), February (2), March (3)...November (11), December (12), January-A (13), February-A (14), March-A (15).

I don't know if there is another way to do this that would be better. Thanks for the input!
I'm new to access and thank you for the file, and all the hard work put into it. I've been playing around with it. Do you have any advice how I could add an extra tab next to photo tab and form to display a map in this flower dB.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:02
Joined
Oct 29, 2018
Messages
21,358
I'm new to access and thank you for the file, and all the hard work put into it. I've been playing around with it. Do you have any advice how I could add an extra tab next to photo tab and form to display a map in this flower dB.
Hi. Welcome to AWF!

This is an 11-year old thread. You might want to start a new thread and add a link to this one.
 

Users who are viewing this thread

Top Bottom