Ok here is some more

Regarding the State, City and Street Tables

If you are going to normalize to that level (Many don’t) then the Street
should have a StreetID as an auto number and in your table location you will
only have the StreetID as Long and not the other fields.

In my view the Location table is good enough - What the determining factor
would be is how accurate the information has to be for and sorting if you
really can’t have 842 S Third and a 842 S. Third in the same city then you
may need to use the StreetId approach.

At the most I would use the City, State but only use to populate the text
fields for default spellings.

Remember you will have to have something in place to add more
combinations to the level you go to. Also - My preference for Id’s are
numbers not the code – and especially for the City table I would use a number
– City names do change (Leningrad)

Regarding Code, Breeding Regions, Breeding Subregions

Anytime you have to use a “,” to separate information that is the same type
you are not normalized. You should have a table for each of them. And then a
linking table to the other tables

For Code:
tblCode
CodeID (autonumber)– I Like to use the number just in case I would have
to change the Code to something else but Still represent the same thing.
Don’t have to worry about updating in all other tables that are linked
Code
Description – I have no idea what a PHY is or a TAX is

Are they codes the same meaning for Order Family and Genus? There may be
more needed. Or change in tables

tlbOrderCodes (and for Family and Genus, Species)
OrderID fk
CodeID fk
Accipitriformes will have 2 entries PHY and TAX
And now you can find the ones that have a TAX Code (easily)

For BreedingRegions

tblBreedingRegions
BreedingRegionID PK
BRCode If you want to keep the 2 letter code
BRName a more descriptive name
…

And now it becomes tricky – Normally I would say this for the standard
Category Sub Category:
tblBreedingSubRegions
BSRID PK
BreedingRegionID fk
BreedingRegionName

tblSpeciesBreedingRegions
SpeciesID
BSRID

The problem is your data for the Sub regions is a mix of different things:

N, SE | se “some area” | “Area1” TO an “Area2” | widespread
These are problems because you couldn’t find that Bird 1’s area is also in
Bird 2’s area (easily)
And:
For the N, SE entries they mean different areas in a Region even though
they have the same ‘name’ (you will have to enter multiple compass references
for each region)

What you will need to do when selecting a SubRegion you will have to
Show/Limit the selection by the region so you get the correct one.

If you do any grouping by Sub Regions ‘Areas’ – Say you wanted everything in
the N in AF you would also have to include all the “areas” that were there
also.

There are other table design options for this but even with the 2 things you
have to keep in mind with this design is it easier than some of the other
solutions.


Not that it matters but I like the primary key for the table at the top. It
is the most important

"cwyse" wrote:

Hi,

I'm trying to create an MS Access database to store bird sightings. I think
I've created all the tables that I need, and tried to form the relationships
as best I could. I'm not very familiar with database design. Is there
anyone out there who would review my tables and relationships?

http://members.cox.net/chriswyse/BirdingDatabase.mdb