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