3 replies to this topic

[itscbiz]

I need to store weekly ranges like for seed packages (planting time) in a database. Does anyone have an idea how to best do that? I want to avoid having too many columns.

sample data:
vegetable #1: sow indoors mid Feb till end March, transplant mid march till mid april, sow outdoors April-end May or August till beginning September
something like that, there can be more than one season for sowing per year, like spring sowing and fall sowing.

any help or idea would be appreciated.

[Orjan]

A table like this would give you what you need:

#, vegetable, action, starttime, endtime
1, 1, sow indoors, mid feb, end march
2, 1, transplant, mid march, mid april
3, 2, sow outdoors, april, end may
4, 2, sow outdoors, august, end september

and a vegetable table
#, vegetable
1, cucumber
2, asparragus
3, onion

of course, the textual representation of start and end can of course be sat to week numbers instead, which is iso standardized way of doing weeks.

#, vegetable, action, starttime, endtime
1, 1, sow indoors, 6, 12 (week 6 to week 12)
2, 1, transplant, 10, 14

if you want text representation on the bags, but not in the database, you can have a table with text to each week number
#, display
1, early january
2, mid january
3, second half january
4, end january
5, early february
6, mid february
7, second half february
8, end february

here, you could also add other values:
100, early spring
101, 2 weeks after snow cover is gone
102, after last frost night

or something like that... that way, you would reuse to a maximum and still get everything working as you want to, if I have understood you correct. the actions could of course also be broken out to a translating table, for non-repeating text values in your database.

[itscbiz]

thank you! I was hoping for something easier. But at least once the complicated programming is done, it will be much easier to use.

[Orjan]

This IS very easy.