Garden database design

[HenBar]

I am trying to design a database which will track information about a
garden. I have several entities in mind so far, the most troublesome noted
below with some of the most important attributes.

PurchaseInfo:
PurchaseIdNo (PK)
Supplier
DatePurchased
TypePurchase (which would contain one of the following items[Plant, Seed,
Misc])
.....

This is where the problem arises. Depending on the data in the TypePurchase
field, one or more things can happen:
1) If TypePurchase is Plant, it will go directly into the Garden as a
Planting (probably another entity containing information such as location,
number of plants, date planted etc.) It is possible (probable actually) to
set out different numbers of plants on different dates in different
locations.

2a) If TypePurchase is Seed, it could be either sown (planted) directly into
the garden where basically the same information as 1) above would need to be
tracked (again should be different numbers of seeds on different dates in
different locations)
2b) or it could be started inside where additional information will need to
be recorded (Light (light/dark), Heat (warm, cool), date sown, number seeds
sown, Transplant (y/n), if Transplant = y, number of transplants, date
transplanted). These seeds (which are now plants) will be planted into the
garden where they will require that the same information as in item 1) above
be recorded.

3) If the type is Misc, it does not go into the garden.

My problem is that I am unsure of the best way create the additional
entities to track the information about how the Seed information should be
recorded.

Thanks in advance for any help anyone can give me with this,
Hank

 

[Amy Blankenship]

I think you need to look at it more as the life cycle of an Item. So

tblItem
ItemID
ItemDesc
ItemType

tblItemTypes
ItemTypeID
ItemTypeDesc

tblItemEvent
EventID
ItemID
EventDate

tblEventDetails
EventID
PropertyDesc
PropertyValue

So by using an EventDesc of "Purchase" with PropertyDesc of "Supplier",
"PurchaseAmount" and values of "Gurney's Seed Co." and $1.29, you can record
a purchase. By recording "Light Level" and "dark", in an event with a
description of "Sowing", you can record that you planted the seed and later
you can record a "Planting" event.

I'd suggest that you also have tblEventType and tblEventTypeProperties to
help you make sure that you fill in the correct properties for each event
and that the same event is always called the same thing. However, it will
*work* as modeled above, just not as well.

Have you considered what the data will look like if you only plant a portion
of a seed packet or devide a plant you bought to place into two separate
places in the garden?

HTH;

Amy

 

[HenBar]

Amy

Thanks for the reply.

I don't think I was all that clear in my original post. In addition to the
table (Entity) I outlined, there were to be others such as PlantingInfo
which would contain information such as location, date planted, what was
planted (purchased plant, seed direct sown into garden or plant from seed
started inside). Another Entity (the reason I am referring to Entities
instead of tables is twofold, 1) I am currently reading a book on Database
Design Using Entity-Relationship Diagrams and 2) the Entities will be
converted to tables once I have the design figured out and map it to the
actual database) would be to store information about seeds sown Inside (this
would be where the light/dark, warm/cold requirements would come in), number
of seeds sown, date sown, whether or not they would be transplanted (if
yes, the date they were transplanted) etc.

The problem I was having was because of the fact that the seed could take
different paths (for example I start some cucumbers inside and sow some
directly into the garden both from the same seed packet)

I must admit that your approach is a very interesting one and I will try to
create a diagram using it.

I would like to attach a copy of the diagram which I have created thus far
but I an unsure of the policy of this newsgroup regarding attachments.

A final note, after I posted my original message I found this link which I
thought was very interesting and quite possibly just what I was looking for:
http://www.utexas.edu/its/windows/database/datamodeling/dm/hierarchies.html

Thanks again, I will let you know how I make out with your suggestions.
Hank

I think you need to look at it more as the life cycle of an Item. So

tblItem
ItemID
ItemDesc
ItemType

tblItemTypes
ItemTypeID
ItemTypeDesc

tblItemEvent
EventID
ItemID
EventDate

tblEventDetails
EventID
PropertyDesc
PropertyValue

So by using an EventDesc of "Purchase" with PropertyDesc of "Supplier",
"PurchaseAmount" and values of "Gurney's Seed Co." and $1.29, you can
record a purchase. By recording "Light Level" and "dark", in an event
with a description of "Sowing", you can record that you planted the seed
and later you can record a "Planting" event.

I'd suggest that you also have tblEventType and tblEventTypeProperties to
help you make sure that you fill in the correct properties for each event
and that the same event is always called the same thing. However, it will
*work* as modeled above, just not as well.

Have you considered what the data will look like if you only plant a
portion of a seed packet or devide a plant you bought to place into two
separate places in the garden?

HTH;

Amy

I am trying to design a database which will track information about a
garden. I have several entities in mind so far, the most troublesome
noted below with some of the most important attributes.

PurchaseInfo:
PurchaseIdNo (PK)
Supplier
DatePurchased
TypePurchase (which would contain one of the following items[Plant, Seed,
Misc])
....

This is where the problem arises. Depending on the data in the
TypePurchase field, one or more things can happen:
1) If TypePurchase is Plant, it will go directly into the Garden as a
Planting (probably another entity containing information such as
location, number of plants, date planted etc.) It is possible (probable
actually) to set out different numbers of plants on different dates in
different locations.

2a) If TypePurchase is Seed, it could be either sown (planted) directly
into the garden where basically the same information as 1) above would
need to be tracked (again should be different numbers of seeds on
different dates in different locations)
2b) or it could be started inside where additional information will need
to be recorded (Light (light/dark), Heat (warm, cool), date sown, number
seeds sown, Transplant (y/n), if Transplant = y, number of transplants,
date transplanted). These seeds (which are now plants) will be planted
into the garden where they will require that the same information as in
item 1) above be recorded.

3) If the type is Misc, it does not go into the garden.

My problem is that I am unsure of the best way create the additional
entities to track the information about how the Seed information should
be recorded.

Thanks in advance for any help anyone can give me with this,
Hank

 

[Amy Blankenship]

Amy

Thanks for the reply.

I don't think I was all that clear in my original post. In addition to
the table (Entity) I outlined, there were to be others such as
PlantingInfo which would contain information such as location, date
planted, what was planted (purchased plant, seed direct sown into garden
or plant from seed started inside). Another Entity (the reason I am
referring to Entities instead of tables is twofold, 1) I am currently
reading a book on Database Design Using Entity-Relationship Diagrams and
2) the Entities will be converted to tables once I have the design
figured out and map it to the actual database) would be to store
information about seeds sown Inside (this would be where the light/dark,
warm/cold requirements would come in), number of seeds sown, date sown,
whether or not they would be transplanted (if yes, the date they were
transplanted) etc.

Calling something an entity doesn't mean that it actually makes a good
candidate for grouping information about it. For instance, seeds, plants,
and tools have an entire life cycle outside their purchase. So the purchase
of these things should not *define* those things. If you think about it,
all of these "entities" have characteristics that define them.

So if you look at the larger picture, the main characteristic about a seed
or plant is its species. And the main characteristic of a tool (I assume
that's the other thing you'd be purchasing) is what type of tool it is. So
to define *a particular* seed, tool, or plant, you would need to create a
new instance (record) of that species or tool type. You may also want to
consider that a packet of seeds will actually become many plant records.

Let's look at the lifetime of a seed packet. A seed packet would experience
several events. First, it would be purchased. Then, it could potentially
be received, if you want to keep tabs on the speed of your suppliers. Next,
the seeds in it would be sown, but it is debatable if that is a seed packet
event, because if you plant some from that packet now and some in a month,
you now have two separate things to track but only one seed packet entity.
Those are all *events* in the life of the seed packet (or group of seeds).
Let me tell you, you are NOT going to be happy if you try to spread those
events around to different tables.

So let's transform the seeds at planting to a "seed starting flat" entity.
One property of the sowing event in the life of the seed starting flat is
the parent seed pack "entity." You'll probably want to continue to record
events for those seeds as a lot until they are old enough to transplant, so
you might also have sprouting and fertilizing events. (You could also just
define the flat as a plant, because plants can be divided as well.)

So now that the seeds have come up and are ready to be transplanted, they
will experience a transplant event and become plant "entities" in their own
right. The transplant event can have an optional property for the parent
seed flat (or plant) to provide traceback. Of course, plants that are
purchased and placed directly in the garden will immediately experience a
transplant event. Seeds that are sown directly could be dealt with as
either seed flats or plants, depending on the circumstances.

The problem I was having was because of the fact that the seed could take
different paths (for example I start some cucumbers inside and sow some
directly into the garden both from the same seed packet)

Yes, that's the reasoning behind having such a flexible table structure.

I must admit that your approach is a very interesting one and I will try
to create a diagram using it.

I would like to attach a copy of the diagram which I have created thus far
but I an unsure of the policy of this newsgroup regarding attachments.

A final note, after I posted my original message I found this link which I
thought was very interesting and quite possibly just what I was looking
for:

http://www.utexas.edu/its/windows/database/datamodeling/dm/hierarchies.html

Basically, they are saying roughly the same thing I was, but said it in
technicalese.

Good luck!

-Amy

 

[HenBar]

Amy

I don't know if you are still checking this thread, if you are I have
created the database pretty much as you suggested, with a couple of small
modifications, and it seems to be a workable solution.

Thanks again for the help and insight
Hank