A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

junction tables for 3 many to many relationships



 
 
Thread Tools Display Modes
  #11  
Old December 21st, 2004, 02:29 PM
Allen Browne
external usenet poster
 
Posts: n/a
Default

LAF, it looks like neither Tim nor I have understood your question.

Hopefully someone else can contribute.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LAF" wrote in message
news [snip]
My original question still stands. Do I need to worry about many to many
relationships among subsets of the 3-field primary key if the 3-filed key
identifies unique records?



  #12  
Old December 21st, 2004, 07:06 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default

=?Utf-8?B?TEFG?= wrote in
news
The 3 field primary key
absolutely identifies a unique record.

Of course it does: the relevant question is whether than means anything!


By the way, the ID's are aluminum bands placed on the legs of birds.
Banding operations can occur at different sites on the same day, and
the time is when we capture a bird in a mist-net. Several birds can
be caught in a mist-net at the same time.


Here's an entity you haven't mentioned before (ps * always denotes the PK
field, rest are obviously optional):

MistNetDeployments(*Place, *DateAndTime, WhoOrganised,
WeatherAtTheTime, SizeOfNet, etc...)

Place is a FK referencing Places, and DateAndTime is a FK referencing
Events, or whatever you have called your tables.

Many of the tables in the database deal with lots of measurements we
take on the birds.


Catchings(*Place, *DateAndTime, *BirdID, Weight, SignsOfHairloss, etc)

note that the FK is (Place, DateAndTime) references MistNetDeployment, and
there is _no_ (repeat, no) FK referencing Places or Events.

For these tables, just ID and Date-Time are
sufficient as primary key to identify unique records.


To identify what exactly? A record is just somewhere to store some facts
about a Thing or an Event or an Occurrence: it has to mean something in the
real world.


I may be making a mountain out of a molehill, but no book on access
deals with junction tables for more than a single many to many
relationship.


A lot of basic texts deal stop at third normal form; you need to look at
more advanced material for 4, 5, and 6. In the end, though, it still makes
pretty much common sense once you have a feel for what you are actually
modelling.

From what you have said, though, it looks like a couple of 1-M
relationships:

Events ----+
^
MistNetDeployments -- Catchings -- Birds
v
Places ----+


The best advice is always to understand your real life business scenario
first, and then the model falls straight out of that. There is general
theory underlying this, but it's no use until you have your own specifics
nailed down flat.

Hope it helps


Tim F

  #13  
Old December 22nd, 2004, 05:58 PM
LeAnne
external usenet poster
 
Posts: n/a
Default

PMFJI, but as a scientist who has both created and consulted
(informally) on designing biological sampling/monitoring databases, may
I offer a suggestion or two?

LAF, it's difficult to grasp your db design from the descriptions you've
posted here. There is clearly some kind of normalization problem that's
preventing you from utilizing the full capabilities of a relational
database. Were I you, I would consider stepping back and reexamining
your entities (real-life persons, places, things, or events that your db
will keep track of) and their attributes (categories of related
information relevent to each entity). Entities & attributes = tables &
fields.

In my experience, managing data from any field survey of a population or
community of organisms will require at least the following entities &
attributes:

Stations - the sampling sites where data are collected. Fields may
include SiteID, SiteName, Latitude, Longitude, County, State, Ecoregion,
and so on.

Events - actual visits (of which there may be one OR MORE) to sites to
collect raw data. Fields may include EventID, SiteID, SampleDate,
SampleTime, ReplicateNum, SampleType, GearUsed (or GearCode), etc.. A
note on SampleType...some developers might argue that it is "easier" to
break up their events tables to reflect the categories of data being
collected (e.g. Benthic, WaterQuality, Habitat). But strictly speaking
this isn't in keeping with the Normal forms.

Organisms - which critters (of which there may be one OR MORE collected
for each unique combination of site+date+time+rep) were collected at
each site for each sampling event , and the number of each. Fields may
include EventID, Taxon (or Taxon ID if you have a taxonomic lookup table
with LatinNames, Taxon level, etc.), and TotalIndividuals.

Organism Details - necessary if you are recording multiple
characteristics or measurements for individual critters. For example:
at Stream X I collected 2 sunfish, 3 bass, and one pickerel. Each fish
was measured for total length, body weight, and whether or not there
were any visible lesions or other anomalies. Fields for the table in
this example may include EventID, TaxonID, FishNum, ParameterMeasured
(or ParameterID if you have a lookup table of standard measurement
parameters and their descriptions), and ParameterValue. You would
probably substitute BandNumber for FishNum to identify individual birds.
You might also want to include a Memo field for comments.

Once your data are organized in a framework that Access understands,
queries become much, much easier. You can calculate the taxonomic
richness at a site for a particular visit, or get a list of all taxa
collected at a site over multiple visits, or generate a list of all
sites where Black-and-White Warblers were collected, or calculate the
proportion of Parula warbler individuals to total abundance of all
individuals at sights visited in Spring, and so on and so on.

Just my $0.02 worth...

Good luck!

LeAnne

LeAnne
  #14  
Old January 3rd, 2005, 07:29 PM
LAF
external usenet poster
 
Posts: n/a
Default

Thanks to Tim, Leanne, and Allen for advice.

The original question about junction tables has become transmorgrified into
general database design. Here I will attempt to integrate and indicate the
aspects of design that I think are relevant. I will list the tables and some
fields with primary key as (PK) and foreign key as (FK):

event table: event.id (PK), bird.id (FK), subsite.id (FK), date, time,
capture.type, observer

bird table: bird.id (PK), species.id (FK), leftleg, rightleg

site table: subsite.id (PK), site, elevation, forest.structure

species table: species (PK), residency, listing

status table: event.id (PK), age, sex, disease, molt, fat, weight

morpho table: event.id (PK), bill, wing, tail, tarsus

sampling table: event.id (PK), bloodlog (FK), bloodquan, feather, pictures

genetics table: bloodlog (PK), event.id (FK), prepnum, malaria, cytb, chd

The major aspect of design here is that the 3 fields in the many to many
relationships (bird.id, subsite.id, date, time [actually 4 fields]) are
indicated by event.id as an autonumbered field. In this case, event.id
serves to indicate the unique combinations of the 4 fields. This is true
because the 4 fields could serve as a composite primary key. What I think is
important here is that the autonumbered field is meaningless except as a
primary key. I read somewhere that composite primary keys can cause some
problems in some queries.

The second aspect of design is that there are several one to one
relationships linked by event.id. In principle, all of these tables can be
merged into the event table, but not all individual birds have all data
taken. The event.id field in all of these additional tables will come from
the event table.

Many of my queries will involve looping over sorted recordsets, such as a
query that could add fields to the bird table such as date first captured,
date last captured. Or identifying individuals that were mis-sexed at
different events.

Happy New Year,

LAF

"Tim Ferguson" wrote:

=?Utf-8?B?TEFG?= wrote in
news
The 3 field primary key
absolutely identifies a unique record.

Of course it does: the relevant question is whether than means anything!


By the way, the ID's are aluminum bands placed on the legs of birds.
Banding operations can occur at different sites on the same day, and
the time is when we capture a bird in a mist-net. Several birds can
be caught in a mist-net at the same time.


Here's an entity you haven't mentioned before (ps * always denotes the PK
field, rest are obviously optional):

MistNetDeployments(*Place, *DateAndTime, WhoOrganised,
WeatherAtTheTime, SizeOfNet, etc...)

Place is a FK referencing Places, and DateAndTime is a FK referencing
Events, or whatever you have called your tables.

Many of the tables in the database deal with lots of measurements we
take on the birds.


Catchings(*Place, *DateAndTime, *BirdID, Weight, SignsOfHairloss, etc)

note that the FK is (Place, DateAndTime) references MistNetDeployment, and
there is _no_ (repeat, no) FK referencing Places or Events.

For these tables, just ID and Date-Time are
sufficient as primary key to identify unique records.


To identify what exactly? A record is just somewhere to store some facts
about a Thing or an Event or an Occurrence: it has to mean something in the
real world.


I may be making a mountain out of a molehill, but no book on access
deals with junction tables for more than a single many to many
relationship.


A lot of basic texts deal stop at third normal form; you need to look at
more advanced material for 4, 5, and 6. In the end, though, it still makes
pretty much common sense once you have a feel for what you are actually
modelling.

From what you have said, though, it looks like a couple of 1-M
relationships:

Events ----+
^
MistNetDeployments -- Catchings -- Birds
v
Places ----+


The best advice is always to understand your real life business scenario
first, and then the model falls straight out of that. There is general
theory underlying this, but it's no use until you have your own specifics
nailed down flat.

Hope it helps


Tim F


  #15  
Old January 4th, 2005, 03:25 PM
LeAnne
external usenet poster
 
Posts: n/a
Default

Hi LAF,

You're very close. However, I suggest:

tblSites 1:m to tblEvents (since each site (subsite?) can be visited on
one OR MORE dates) on SiteID

tblEvents would have EventID as PK. EventID would be an autonumber
providing each unique combination of site+date+time (+capture type if
your are using more than one per event)

tblEvents 1:m to a BirdsCaptured table (since each event can capture one
OR MORE birds) on EventID

The BirdsCaptured table would be the junction table between tblEvents
and tblBirds (since a bird could conceivably be captured one OR MORE
times), and contain BOTH EventID and BirdID as key fields. NetNum could
also be included as a field if multiple nets are being deployed per event.

tblBirdsCaptured m:1 to tblBirds on BirdID (again, each individual bird
may be captured one OR MORE times)

tblBirds m:1 to tblSpecies (assuming that one OR MORE individuals in a
species have been banded) on SpeciesID

tblSpecies is the lookup table with taxon-specific information.

Now, if I read your explanation right, there are multiple parameters
measured for each bird captured (morphological characteristics, age,
genetics, &c.). Not all birds may be measured for all parameters. And
each time a bird is recaptured, some (but not all) parameters are
measured again. Correct? If so, then I would recommend that measurements
be combined into a single Measurements table, joined m:1 to
tblBirdsCaptured on BirdID + EventID. tblMeasurements would include the
fields EventID, BirdID, MeasurementID, and MeasurementValue.
tblMeasurements would be joined m:1 to a MeasurementLookup table on
MeasurementID with MeasurementName, MeasurementUnit, MeasurementCategory
(morpho, genetic, etc.), Description, MethodUsed, etc. included as
fields. Measurements are data, not attributes; by expanding measurements
down instead of across, you eliminate empty fields when a particular
parameter is not recorded for a particular bird captured in a particular
event.

Hope this helps,

LeAnne

LAF wrote:

Thanks to Tim, Leanne, and Allen for advice.

The original question about junction tables has become transmorgrified into
general database design. Here I will attempt to integrate and indicate the
aspects of design that I think are relevant. I will list the tables and some
fields with primary key as (PK) and foreign key as (FK):

event table: event.id (PK), bird.id (FK), subsite.id (FK), date, time,
capture.type, observer

bird table: bird.id (PK), species.id (FK), leftleg, rightleg

site table: subsite.id (PK), site, elevation, forest.structure

species table: species (PK), residency, listing

status table: event.id (PK), age, sex, disease, molt, fat, weight

morpho table: event.id (PK), bill, wing, tail, tarsus

sampling table: event.id (PK), bloodlog (FK), bloodquan, feather, pictures

genetics table: bloodlog (PK), event.id (FK), prepnum, malaria, cytb, chd

The major aspect of design here is that the 3 fields in the many to many
relationships (bird.id, subsite.id, date, time [actually 4 fields]) are
indicated by event.id as an autonumbered field. In this case, event.id
serves to indicate the unique combinations of the 4 fields. This is true
because the 4 fields could serve as a composite primary key. What I think is
important here is that the autonumbered field is meaningless except as a
primary key. I read somewhere that composite primary keys can cause some
problems in some queries.

The second aspect of design is that there are several one to one
relationships linked by event.id. In principle, all of these tables can be
merged into the event table, but not all individual birds have all data
taken. The event.id field in all of these additional tables will come from
the event table.

Many of my queries will involve looping over sorted recordsets, such as a
query that could add fields to the bird table such as date first captured,
date last captured. Or identifying individuals that were mis-sexed at
different events.

Happy New Year,

LAF

  #16  
Old January 4th, 2005, 04:19 PM
LAF
external usenet poster
 
Posts: n/a
Default

Hi LeAnne,

Thanks so much. I will get back to the group on the suggestions. However,
one point that could be made now. The condition data like age, sex, molt,
disease, etc, are really attributes. The sampling data are also really
attributes. The morphometric data are really the values. I am trying to
think of how the measurement table as you identify could be used for
statistical analysis. I frequently bring tables into the statistical program
S-plus, where I have the fields (columns) with BirdId, Species, Yr, and many
of the condition and morphometric fields. Even if the 1:1 table
relationships that I proposed have lots of fields with missing values, it is
easy either in access or in S-plus to just take the subset of records with
non-missing values.

Might it would be better to have the condition fields and the sampling
fields in the event table? I have to think of how I could get tables into
S-plus in which the rows are observations and columns are values.

Thanks,

LAF

"LeAnne" wrote:

Hi LAF,

You're very close. However, I suggest:

tblSites 1:m to tblEvents (since each site (subsite?) can be visited on
one OR MORE dates) on SiteID

tblEvents would have EventID as PK. EventID would be an autonumber
providing each unique combination of site+date+time (+capture type if
your are using more than one per event)

tblEvents 1:m to a BirdsCaptured table (since each event can capture one
OR MORE birds) on EventID

The BirdsCaptured table would be the junction table between tblEvents
and tblBirds (since a bird could conceivably be captured one OR MORE
times), and contain BOTH EventID and BirdID as key fields. NetNum could
also be included as a field if multiple nets are being deployed per event.

tblBirdsCaptured m:1 to tblBirds on BirdID (again, each individual bird
may be captured one OR MORE times)

tblBirds m:1 to tblSpecies (assuming that one OR MORE individuals in a
species have been banded) on SpeciesID

tblSpecies is the lookup table with taxon-specific information.

Now, if I read your explanation right, there are multiple parameters
measured for each bird captured (morphological characteristics, age,
genetics, &c.). Not all birds may be measured for all parameters. And
each time a bird is recaptured, some (but not all) parameters are
measured again. Correct? If so, then I would recommend that measurements
be combined into a single Measurements table, joined m:1 to
tblBirdsCaptured on BirdID + EventID. tblMeasurements would include the
fields EventID, BirdID, MeasurementID, and MeasurementValue.
tblMeasurements would be joined m:1 to a MeasurementLookup table on
MeasurementID with MeasurementName, MeasurementUnit, MeasurementCategory
(morpho, genetic, etc.), Description, MethodUsed, etc. included as
fields. Measurements are data, not attributes; by expanding measurements
down instead of across, you eliminate empty fields when a particular
parameter is not recorded for a particular bird captured in a particular
event.

Hope this helps,

LeAnne

LAF wrote:

Thanks to Tim, Leanne, and Allen for advice.

The original question about junction tables has become transmorgrified into
general database design. Here I will attempt to integrate and indicate the
aspects of design that I think are relevant. I will list the tables and some
fields with primary key as (PK) and foreign key as (FK):

event table: event.id (PK), bird.id (FK), subsite.id (FK), date, time,
capture.type, observer

bird table: bird.id (PK), species.id (FK), leftleg, rightleg

site table: subsite.id (PK), site, elevation, forest.structure

species table: species (PK), residency, listing

status table: event.id (PK), age, sex, disease, molt, fat, weight

morpho table: event.id (PK), bill, wing, tail, tarsus

sampling table: event.id (PK), bloodlog (FK), bloodquan, feather, pictures

genetics table: bloodlog (PK), event.id (FK), prepnum, malaria, cytb, chd

The major aspect of design here is that the 3 fields in the many to many
relationships (bird.id, subsite.id, date, time [actually 4 fields]) are
indicated by event.id as an autonumbered field. In this case, event.id
serves to indicate the unique combinations of the 4 fields. This is true
because the 4 fields could serve as a composite primary key. What I think is
important here is that the autonumbered field is meaningless except as a
primary key. I read somewhere that composite primary keys can cause some
problems in some queries.

The second aspect of design is that there are several one to one
relationships linked by event.id. In principle, all of these tables can be
merged into the event table, but not all individual birds have all data
taken. The event.id field in all of these additional tables will come from
the event table.

Many of my queries will involve looping over sorted recordsets, such as a
query that could add fields to the bird table such as date first captured,
date last captured. Or identifying individuals that were mis-sexed at
different events.

Happy New Year,

LAF


  #17  
Old January 4th, 2005, 04:48 PM
LeAnne
external usenet poster
 
Posts: n/a
Default

Hi LAF,

Sorry, this is incorrect. Condition categories like age, sex, molt,
disease, etc. are *data*, not attributes (fields). The values recorded
for each category are also *data.* In a properly relational design,
tables expand down, not across. With a table design using conditions as
attributes, each time you want to add an additional measurement
category, you have to add a new field. All queries you have based on the
table will then have to be changed manually. How data are viewed or
exported should not affect table design. Tables are for *storing* data.
The design I suggested is normalized for a relational model. Use queries
to *display* the data in a wide-flat arrangement...look up "Crosstab
queries" in the Help file. You can export query recordsets as .xls or
..dbf files (File Save As/Export To an External File or Database),
which I believe SAS can handle easily.

L.

LAF wrote:

Hi LeAnne,

Thanks so much. I will get back to the group on the suggestions. However,
one point that could be made now. The condition data like age, sex, molt,
disease, etc, are really attributes. The sampling data are also really
attributes. The morphometric data are really the values. I am trying to
think of how the measurement table as you identify could be used for
statistical analysis. I frequently bring tables into the statistical program
S-plus, where I have the fields (columns) with BirdId, Species, Yr, and many
of the condition and morphometric fields. Even if the 1:1 table
relationships that I proposed have lots of fields with missing values, it is
easy either in access or in S-plus to just take the subset of records with
non-missing values.

Might it would be better to have the condition fields and the sampling
fields in the event table? I have to think of how I could get tables into
S-plus in which the rows are observations and columns are values.

Thanks,

LAF

"LeAnne" wrote:


Hi LAF,

You're very close. However, I suggest:

tblSites 1:m to tblEvents (since each site (subsite?) can be visited on
one OR MORE dates) on SiteID

tblEvents would have EventID as PK. EventID would be an autonumber
providing each unique combination of site+date+time (+capture type if
your are using more than one per event)

tblEvents 1:m to a BirdsCaptured table (since each event can capture one
OR MORE birds) on EventID

The BirdsCaptured table would be the junction table between tblEvents
and tblBirds (since a bird could conceivably be captured one OR MORE
times), and contain BOTH EventID and BirdID as key fields. NetNum could
also be included as a field if multiple nets are being deployed per event.

tblBirdsCaptured m:1 to tblBirds on BirdID (again, each individual bird
may be captured one OR MORE times)

tblBirds m:1 to tblSpecies (assuming that one OR MORE individuals in a
species have been banded) on SpeciesID

tblSpecies is the lookup table with taxon-specific information.

Now, if I read your explanation right, there are multiple parameters
measured for each bird captured (morphological characteristics, age,
genetics, &c.). Not all birds may be measured for all parameters. And
each time a bird is recaptured, some (but not all) parameters are
measured again. Correct? If so, then I would recommend that measurements
be combined into a single Measurements table, joined m:1 to
tblBirdsCaptured on BirdID + EventID. tblMeasurements would include the
fields EventID, BirdID, MeasurementID, and MeasurementValue.
tblMeasurements would be joined m:1 to a MeasurementLookup table on
MeasurementID with MeasurementName, MeasurementUnit, MeasurementCategory
(morpho, genetic, etc.), Description, MethodUsed, etc. included as
fields. Measurements are data, not attributes; by expanding measurements
down instead of across, you eliminate empty fields when a particular
parameter is not recorded for a particular bird captured in a particular
event.

Hope this helps,

LeAnne

LAF wrote:


Thanks to Tim, Leanne, and Allen for advice.

The original question about junction tables has become transmorgrified into
general database design. Here I will attempt to integrate and indicate the
aspects of design that I think are relevant. I will list the tables and some
fields with primary key as (PK) and foreign key as (FK):

event table: event.id (PK), bird.id (FK), subsite.id (FK), date, time,
capture.type, observer

bird table: bird.id (PK), species.id (FK), leftleg, rightleg

site table: subsite.id (PK), site, elevation, forest.structure

species table: species (PK), residency, listing

status table: event.id (PK), age, sex, disease, molt, fat, weight

morpho table: event.id (PK), bill, wing, tail, tarsus

sampling table: event.id (PK), bloodlog (FK), bloodquan, feather, pictures

genetics table: bloodlog (PK), event.id (FK), prepnum, malaria, cytb, chd

The major aspect of design here is that the 3 fields in the many to many
relationships (bird.id, subsite.id, date, time [actually 4 fields]) are
indicated by event.id as an autonumbered field. In this case, event.id
serves to indicate the unique combinations of the 4 fields. This is true
because the 4 fields could serve as a composite primary key. What I think is
important here is that the autonumbered field is meaningless except as a
primary key. I read somewhere that composite primary keys can cause some
problems in some queries.

The second aspect of design is that there are several one to one
relationships linked by event.id. In principle, all of these tables can be
merged into the event table, but not all individual birds have all data
taken. The event.id field in all of these additional tables will come from
the event table.

Many of my queries will involve looping over sorted recordsets, such as a
query that could add fields to the bird table such as date first captured,
date last captured. Or identifying individuals that were mis-sexed at
different events.

Happy New Year,

LAF


  #18  
Old January 5th, 2005, 05:01 PM
LAF
external usenet poster
 
Posts: n/a
Default

Hi LeAnne,

I think I understand the design. However, the measurement value field in
tblMeasurements will contain numeric data for some measurements, text data
for some measurements, and logical data for still others. I am not aware of
access's ability to mix these data types in the same field.

Thanks, and all the best,

LAF



"LeAnne" wrote:

Hi LAF,

Sorry, this is incorrect. Condition categories like age, sex, molt,
disease, etc. are *data*, not attributes (fields). The values recorded
for each category are also *data.* In a properly relational design,
tables expand down, not across. With a table design using conditions as
attributes, each time you want to add an additional measurement
category, you have to add a new field. All queries you have based on the
table will then have to be changed manually. How data are viewed or
exported should not affect table design. Tables are for *storing* data.
The design I suggested is normalized for a relational model. Use queries
to *display* the data in a wide-flat arrangement...look up "Crosstab
queries" in the Help file. You can export query recordsets as .xls or
..dbf files (File Save As/Export To an External File or Database),
which I believe SAS can handle easily.

L.

LAF wrote:

Hi LeAnne,

Thanks so much. I will get back to the group on the suggestions. However,
one point that could be made now. The condition data like age, sex, molt,
disease, etc, are really attributes. The sampling data are also really
attributes. The morphometric data are really the values. I am trying to
think of how the measurement table as you identify could be used for
statistical analysis. I frequently bring tables into the statistical program
S-plus, where I have the fields (columns) with BirdId, Species, Yr, and many
of the condition and morphometric fields. Even if the 1:1 table
relationships that I proposed have lots of fields with missing values, it is
easy either in access or in S-plus to just take the subset of records with
non-missing values.

Might it would be better to have the condition fields and the sampling
fields in the event table? I have to think of how I could get tables into
S-plus in which the rows are observations and columns are values.

Thanks,

LAF

"LeAnne" wrote:


Hi LAF,

You're very close. However, I suggest:

tblSites 1:m to tblEvents (since each site (subsite?) can be visited on
one OR MORE dates) on SiteID

tblEvents would have EventID as PK. EventID would be an autonumber
providing each unique combination of site+date+time (+capture type if
your are using more than one per event)

tblEvents 1:m to a BirdsCaptured table (since each event can capture one
OR MORE birds) on EventID

The BirdsCaptured table would be the junction table between tblEvents
and tblBirds (since a bird could conceivably be captured one OR MORE
times), and contain BOTH EventID and BirdID as key fields. NetNum could
also be included as a field if multiple nets are being deployed per event.

tblBirdsCaptured m:1 to tblBirds on BirdID (again, each individual bird
may be captured one OR MORE times)

tblBirds m:1 to tblSpecies (assuming that one OR MORE individuals in a
species have been banded) on SpeciesID

tblSpecies is the lookup table with taxon-specific information.

Now, if I read your explanation right, there are multiple parameters
measured for each bird captured (morphological characteristics, age,
genetics, &c.). Not all birds may be measured for all parameters. And
each time a bird is recaptured, some (but not all) parameters are
measured again. Correct? If so, then I would recommend that measurements
be combined into a single Measurements table, joined m:1 to
tblBirdsCaptured on BirdID + EventID. tblMeasurements would include the
fields EventID, BirdID, MeasurementID, and MeasurementValue.
tblMeasurements would be joined m:1 to a MeasurementLookup table on
MeasurementID with MeasurementName, MeasurementUnit, MeasurementCategory
(morpho, genetic, etc.), Description, MethodUsed, etc. included as
fields. Measurements are data, not attributes; by expanding measurements
down instead of across, you eliminate empty fields when a particular
parameter is not recorded for a particular bird captured in a particular
event.

Hope this helps,

LeAnne

LAF wrote:


Thanks to Tim, Leanne, and Allen for advice.

The original question about junction tables has become transmorgrified into
general database design. Here I will attempt to integrate and indicate the
aspects of design that I think are relevant. I will list the tables and some
fields with primary key as (PK) and foreign key as (FK):

event table: event.id (PK), bird.id (FK), subsite.id (FK), date, time,
capture.type, observer

bird table: bird.id (PK), species.id (FK), leftleg, rightleg

site table: subsite.id (PK), site, elevation, forest.structure

species table: species (PK), residency, listing

status table: event.id (PK), age, sex, disease, molt, fat, weight

morpho table: event.id (PK), bill, wing, tail, tarsus

sampling table: event.id (PK), bloodlog (FK), bloodquan, feather, pictures

genetics table: bloodlog (PK), event.id (FK), prepnum, malaria, cytb, chd

The major aspect of design here is that the 3 fields in the many to many
relationships (bird.id, subsite.id, date, time [actually 4 fields]) are
indicated by event.id as an autonumbered field. In this case, event.id
serves to indicate the unique combinations of the 4 fields. This is true
because the 4 fields could serve as a composite primary key. What I think is
important here is that the autonumbered field is meaningless except as a
primary key. I read somewhere that composite primary keys can cause some
problems in some queries.

The second aspect of design is that there are several one to one
relationships linked by event.id. In principle, all of these tables can be
merged into the event table, but not all individual birds have all data
taken. The event.id field in all of these additional tables will come from
the event table.

Many of my queries will involve looping over sorted recordsets, such as a
query that could add fields to the bird table such as date first captured,
date last captured. Or identifying individuals that were mis-sexed at
different events.

Happy New Year,

LAF



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy Multiple Tables with Relationships dang nguyen via AccessMonster.com Running & Setting Up Queries 3 December 7th, 2004 02:22 AM
Help with Junction Tables and Subforms Maureen Smith New Users 11 September 23rd, 2004 02:39 PM
Importing tables and Relationships Janine New Users 4 August 24th, 2004 11:13 AM
Relationships between tables Mel New Users 2 June 11th, 2004 04:00 PM
Need help with Tables Design and Relationships Tom Database Design 24 May 19th, 2004 06:51 PM


All times are GMT +1. The time now is 04:36 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.