By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,400 Members | 981 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,400 IT Pros & Developers. It's quick & easy.

Table Design Question...

P: n/a
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

Nov 16 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
--
Darryl Kerkeslager


Nov 16 '06 #2

P: n/a
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

Nov 17 '06 #3

P: n/a
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

Nov 17 '06 #4

P: n/a
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?

--
Darryl Kerkeslager

"Regnab" <p.*******@gmail.comwrote
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

Nov 17 '06 #5

P: n/a
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

Nov 17 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.