many to many relationship

[anil]

hi all.
I need some information regarding relationships.

tables are tblMONTH(monthId,month,...)
tblSITE(SiteId,Site,....)
tblParameter(Parameterid,parameter,....)

here in each month we do samples for different parameters in different
sites.

so each month will have many sites and each site can be in many months.
Similarly each sites have many parameters and each parameter can be
tested in many sites.

So my problem is Do I need to make 'function' table like
tblMonthSite(MSid,monthid,siteid,...) for tblsite and tblMonth and
tblSiteParameter(PSid,siteid,parameterid,....) for tblSIte and
tblParameter as all three are many to many relationship.

or can I make
tblMonthSiteParameter(MSPid,monthid,siteid,parameterid,...) is this
correct.

thanks
anil

 

[anil]

hi all
the resullt look like this
where january is month,SlAra001,.... are site codes and remianing
BOd,pH etc are parameters and x means this parameters in these sites in
january.
Similar is for other months.
Is this 3rd normal form or greater than that.i am bit confused

Month SiteCode Parameter
January BOD pH SS E.Coli Total P Total N SAR EC
SLARA001 x x x x x x x
SOARA002 x x x x
SOARA004 x x x
SOARA005 x x x
SOARA008 x x x
TTARA001 xx xx xx xx
TTARA002 xx xx xx xx
GOARA011 x x
GOARA012 x x

thanks
anil

 

[John Vinson]

hi all
the resullt look like this
where january is month,SlAra001,.... are site codes and remianing
BOd,pH etc are parameters and x means this parameters in these sites in
january.
Similar is for other months.
Is this 3rd normal form or greater than that.i am bit confused

Month SiteCode Parameter
January BOD pH SS E.Coli Total P Total N SAR EC
SLARA001 x x x x x x x
SOARA002 x x x x
SOARA004 x x x
SOARA005 x x x
SOARA008 x x x
TTARA001 xx xx xx xx
TTARA002 xx xx xx xx
GOARA011 x x
GOARA012 x x

thanks
anil

Sorry, that isn't even in second normal form: you have data
(paramters) as fieldnames, a classic example of "comitting
spreadsheet".

I'd change the name of the Month field (it's a reserved word) and even
recommend using a Date/Time datatype, MeasurementDate, so you can sort
chronologically (month names sort alphabetically of course); in
addition, there'll be ANOTHER January, in just a few more months. Try:

Sites
SiteCode <primary key>
<other info about the site>

Parameters
ParmeterName <e.g. BOD, pH, SS, ...>
<info about the paramter, if any>

Measurements
MeasurementID Autonumber Primary Key
MeasurementDate <e.g. #1/1/2006#>
Parameter <link to Parameters>
Site <link to Sites>
Measurement <the value of BOD or whatever the parameter might be>


John W. Vinson[MVP]

 

[anil]

Thanks john for reply

I agree with this.With site and parameter it is ok.
May be I could not explain properly last time.

Actually I need in output format (MEASUREMENT AS MT) as--

MTID - MTDate - Site - Parameter
1 - 1/1/2006(Jan) - ARA001- BOD
2 - 1/1/2006(Jan) - ARA001 -pH
3 - 1/1/2006(Jan) - ARA001- EC
4 - 1/1/2006(Jan) - ARA002 -BOD
---------------------------------------

Actually it is schedule which is monthly basis and states that for each
month(say January) we have to sample Sites for different parameters.
Thus we have different sites in different months and different
parameters in different sites in each month.

e.g in jan we test ara001 for 5 parameters and feb we test ara001 for 3
parameters and in march we test ara001 for 8 parameters and so on.
similarly for Ara002 3 parameters in jan,5 in feb and so on.

we don't have any value simply tell that we need to sample for these
parameters.This schedule will be fixed for whole year.

So this thing is confusing me how to break above table.

hope I could expalin this time properly
thanks
anil

 

[anil]

One more point as per I have understood
all three tables site,month/mt and parameter have many to many
relationship with each other.

as many months can have many site and many parameters.

like jan can have ara001 and bod,ph,ec etc. and
similarly ara001 can be in jan ,feb ......
bod ,ph can be in jan ,feb,..... and ara001,ara002 ......

Hope this expalins better.
thanks
anil