Table Design Question...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Regnab - 16 Nov 2006 21:49 GMT

I have a table "tblSprayApplication" in a project for a nursery. Each
record refers to a single spray application across the nursery,
recording which groups had been sprayed. Multiple applications (in
different parts of the greenhouse) can occur targetting the same set of
pests. My question is how should I best record this? Originally I was
going to have just the two tables recording the pests targetted for
each application. However, there might be 50 spray applications for the
one combination of pests... and this same combination might be target
every 2 months. I thought of having the pests as boolean fields in the
targetted table but thought that would constrict the system if new
pests were to be added later. The only way I could think of doing it
would be to have each pest combination (as it arose) listed in
tblSprayTargetCombination - something like this:

tblSprayApplication
sa_RecID_ato - (link)
sa_Date_dte
sa_CropStage_txt
etc....

tblSprayApplicationTargets
sat_RecID_ato
sat_SPRAYAPPLICATIONID_int (FK1)
sat_COMBOGROUP_int (Link)

tblSprayTargetCombination
stc_RecID_ato
stc_ComboGroup_int (FK1)
stc_Target_txt

So tblSprayTargetCombination would look something like this (with
possibly more scary sounding pests). For the record, the Target field
actually references a lookup table so it would be an integer in the
Target field:

stc_RecID_ato    stc_ComboGroup_int    stc_Target_txt
1    1    Green Bug
2    1    Hungry Beetle
3    1    Cranky Bug
4    2    Green Bug
5    2    Hungry Beetle
6    3    Green Bug
7    3    Hungry Beetle
8    3    Cranky Bug
9    3    Sour Mite

Anyways, all thought and/or feedback welcome (along with any
constructive critisism).

Cheers

Reg

Reply to this Message

Darryl Kerkeslager - 16 Nov 2006 22:54 GMT

This is a basic Many to Many relationship: You have many spray applications
that you do for many pests.

In this type of relationship, it is standard to create a linking table. You
attempted to do this, but the manner you attempted is non-standard.

spray_application
{spray_application_id} PK
{sa_date}
{sa_crop_stage}

nasty_pest
{nasty_pest_id} PK
{np_name}
{np_preferred_pesticide_id} FK table pesticide

pesticide
{pesticide_id} PK
{psti_name}

LINKING TABLE (option 1):

spray_nasty_pest
{spray_application_id} FK table spray_application
{nasty_pest_id} FK table nasty_pest
* both fields together form the PK for this table.

LINKING TABLE (option 2):

spray_nast_pest
{sn_id} PK
{spray_application_id} FK table spray_application
{nasty_pest_id} FK table nasty_pest

Signature

Darryl Kerkeslager

Reply to this Message

Regnab - 17 Nov 2006 02:10 GMT

Thanks Darryl,

That makes good sense. That was the way I initially considered it - I
was just thinking about the fact that the same pests would be listed
against multiple Spray Applications. But if this is an accepted way of
doing it - no worries, I'll run it this way.

My only real concern is someone coming in later and going "Why did he
do it that way", so just wanted to check how others would handle it.

Thanks again,

Cheers

Reg

Reply to this Message

Regnab - 17 Nov 2006 03:58 GMT

Just another quick question if I may - I'm try to decide the balance
between more tables or empty spaces in tables.

Firstly, regarding the multiple tables. In the nursery, there is spray
applications, fertiliser applications and irrigation application. Each
of these become tables, and relate to multiple sections. I have a table
for sections of the nursery- the question is do I create three tables
to link each application table to the section table (which is easy but
has three tables), or is it better to have one linking table with an
"ApplicationDesc" along with the ApplicationID and SectionID?? If I do
it that way, how does that effect queries - I imagine i would then just
have to specify the ApplicationDesc when joining the tables? Is this
considered appropriate?

tblFertiliserApp
[f_FertID_ato]
[f_FertAmount_int]

tblSprayApp
[s_SprayID_ato]
[s_SprayAmout_int]

tblIrrigationApp
[i_IrrigationID_ato]
[i_IrrAmount_int]

tblApplicationSectionLink
[as_ApplicationDesc]
[as_ApplicationID]FK
[as_SectionID]

I might skip on the empty fields question bit for now....

Thanks again,

Reg

Reply to this Message

Darryl Kerkeslager - 17 Nov 2006 04:54 GMT

With only some basic knowledge of nursery operations, I can only guess that
there are some pretty significant differences between your pesticides,
fertilizers, and irrigation.
Yes, they could all be a type of application, in which case you would do
something like this:

application
[application_id] PK
[app_apply_type_id] FK table apply_type
[app_amount]

apply_type
[apply_type_id] PK
[at_desc] (fertilizer, pesticide, irrigation)

section
[section_id] PK
[sec_description]

application_section_link
[as_application_id] FK
[as_section_id] FK

That way, they apply_type is easily linked into queries.

My guess is, however, that there are significant important differences
between fertilizers, pesticides, and irrigation, and that they are different
enough that you want to capture more than just that they are types of
applications. You could do this, to create a hierarchal table structure, if
they share several inherited characteristics from application, and have only
a few characteristics of their own:

application
[application_id] PK
[app_amount]

pesticide
[application_id] FK table application, 1 to 1 relation
[pst_lethalness]

fertilizer
[application_id] FK table application, 1 to 1 relation
[frt_is_liquid]

irrigation
[application_id] FK table application, 1 to 1 relation
[irr_duration]

section
[section_id] PK
[sec_description]

application_section_link
[as_application_id] FK
[as_section_id] FK

Your queries then JOIN section
JOIN application_section
JOIN application
JOIN (specified table)
which looks complex, but is very easy in query designer.

Of course you could also do it the other way, if fertilizers, pesticides,
and irrigation have little in common, and the first two have so many
potential characteristics, that any benefit of being in a hierarchal
structure would be minimal compared to the hassle. So ...

fertilizer
[fertilizer_id] PK
[frt_amount]
[frt_brand]
[frt_water_mix]

pesticide
[pesticide_id] PK
[pst_amount]
[pst_brand]
[pst_list_o_pests]

irrigation
[irrigation_id] PK
[irr_amount]

section
[section_id] PK
[sec_description]

fertilizer_section_link
[fs_fertilizer_id] FK
[fs_section_id] FK

pesticide_section_link
[ps_pesticide_id] FK
[ps_section_id] FK

irrigation_section_link
[is_irrigation_id] FK
[is_section_id] FK

Your queries are simpler:
JOIN section
JOIN irrigation_section
JOIN irrigation
But you've increased the tables, and possibly lost a valuable ability to
query all amount fields at once without a UNION query.

Ask yourself,
Are these 3 colors of the same make and model car?
Are these 3 models of cars?
or
Are these 3 objects that all just happen to move?

Signature

Darryl Kerkeslager

> Firstly, regarding the multiple tables. In the nursery, there is spray
> applications, fertiliser applications and irrigation application. Each
[quoted text clipped - 29 lines]
>
> Reg

Reply to this Message

Regnab - 17 Nov 2006 05:34 GMT

Thanks again Darryl,

The car example is a good way of thinking about it. Fertilising,
Spraying and Irrigating are different operations which have different
fields and different dependent tables related to them. What I was
thinking about it that they all have a "tblApplicationSection"
associated with them (a nursery is divided into a number of different
sections or areas)- the different sections where the one application
(spray, fertiliser, irrigation) has occured. I have a "tblSection" with
section details, and tblApplicationSection links the two.

I think I might go with the seperate tables because it'll be a lot
cleaner as far as queries are concerned. Being able to search them
together is not really required.

Thanks again for your quick response,

Cheers

Reg

Reply to this Message