Birding Life List design

[cwyse]

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

 

[CraigH]

I just did a quick look.

Major problem - change all memos that won't go over 255 characters to Text
(each entery in a memo 'I think' still takes 2k of space minimum)

Between Location and Sitings & Sitings and Species you can enforce
Referential Integrity.

maybee more later

 

[CraigH]

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 via AccessMonster.com]

Hi Craig,

Thanks for the great posts. I really appreciate the help. I'll take a look
at your comments tonight and get back to you tonight with answers to your
questions.

Chris

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

Hi,

[quoted text clipped - 4 lines]

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

 

[John W. Vinson]

I just did a quick look.

Major problem - change all memos that won't go over 255 characters to Text
(each entery in a memo 'I think' still takes 2k of space minimum)

18 actually (there may be a one-time 2000 byte allocation for the Memo page).
But it's still good advice, since Memos can't be indexed and are more prone to
corruption than Text fields.

 

[cwyse via AccessMonster.com]

Hi,

John and Craig - thanks for the information and help.

I've updated the database with the changes you suggested with the following
exceptions. First, I kept the locations down to the street level. I want to
be able to search on locations without worrying about misspellings, and I'd
like to be able to use the values for states and countries as drop downs on a
form. Second, I didn't make any changes to the regions. I should have
clarified in the first post - I forgot there was region information in there.
The original bird related information was imported directly from a converted
XML file from the IOC (International Ornithological Congress). I want to
keep those tables the same so that I can obtain updates when they publish
them.

The database link now points to the updated database.

I'm thinking that the database tables and relationships are pretty close to
what I need. I may post again when I get a little further with putting
together a form for record input.

Actually, I have a question on the form right now - I want my form to update
multiple tables at once. I'd like to be able to add photo information, new
locations, and reference birds all from the same screen. When I hit the
forward button, all the tables would be updated with the information on the
screen. Additionally, each successively detailed location information would
create a different drop down menu while I'm inputting. For instance, if I
chose the state of Connecticut, only cities in CT would be listed. Further,
the city table would be populated as I have sightings in that city. So for
the city selection, I could either choose from the drop down list, or I could
type in a new city which would be added to the table.

What's the best way to create something like this? For my location drop
downs, I've been adding VB code to set the RowSource to an appropriate select
statement, the issue a requery. For instance, when the city is selected, I
update the RowSource for the street to a select statement that queries
streets in that city, then issue a reQuery call.

I'm thinking that for the photos, I should have a spreadsheet widget on the
form that allows me to enter one or more photo records.

Should I update the records in every table at once? Is there a standard way
of doing this? I need to update the location records, sightings, and photos.
I'm not quite sure how to organize it, if it all requires VB code, and how to
update the records.

Any comments on the form creation? Are my tables and relationships "good to
go"?

Chris

FYI.. My background - I'm mainly a C programmer (22 years). I've done a
little work creating some Oracle forms (for about 6 months, and it was 8
years ago). I've also done a couple of years of VB work, about 6 years ago.
So I can probably fumble through with some fairly technical information, but
some pointers in the right direction would make things a lot easier.

Hi Craig,

Thanks for the great posts. I really appreciate the help. I'll take a look
at your comments tonight and get back to you tonight with answers to your
questions.

Chris

Ok here is some more

[quoted text clipped - 89 lines]

 

[cjwagner]

cwyse,
have you thought about tagging your photos with lat,lon. I came across a
free program called 'Geosetter' ( http://www.geosetter.de/en/ ) where you
can manually set the lattitude an longitude toi a photo using a map. One
could possibably put that info into a table that could eventually be used on
google earth. If you are using a GPS with tracking abilities it can do it
automatically. but my mind wanders.

best of luck.

Hi,

John and Craig - thanks for the information and help.

I've updated the database with the changes you suggested with the following
exceptions. First, I kept the locations down to the street level. I want to
be able to search on locations without worrying about misspellings, and I'd
like to be able to use the values for states and countries as drop downs on a
form. Second, I didn't make any changes to the regions. I should have
clarified in the first post - I forgot there was region information in there.
The original bird related information was imported directly from a converted
XML file from the IOC (International Ornithological Congress). I want to
keep those tables the same so that I can obtain updates when they publish
them.

The database link now points to the updated database.

I'm thinking that the database tables and relationships are pretty close to
what I need. I may post again when I get a little further with putting
together a form for record input.

Actually, I have a question on the form right now - I want my form to update
multiple tables at once. I'd like to be able to add photo information, new
locations, and reference birds all from the same screen. When I hit the
forward button, all the tables would be updated with the information on the
screen. Additionally, each successively detailed location information would
create a different drop down menu while I'm inputting. For instance, if I
chose the state of Connecticut, only cities in CT would be listed. Further,
the city table would be populated as I have sightings in that city. So for
the city selection, I could either choose from the drop down list, or I could
type in a new city which would be added to the table.

What's the best way to create something like this? For my location drop
downs, I've been adding VB code to set the RowSource to an appropriate select
statement, the issue a requery. For instance, when the city is selected, I
update the RowSource for the street to a select statement that queries
streets in that city, then issue a reQuery call.

I'm thinking that for the photos, I should have a spreadsheet widget on the
form that allows me to enter one or more photo records.

Should I update the records in every table at once? Is there a standard way
of doing this? I need to update the location records, sightings, and photos.
I'm not quite sure how to organize it, if it all requires VB code, and how to
update the records.

Any comments on the form creation? Are my tables and relationships "good to
go"?

Chris

FYI.. My background - I'm mainly a C programmer (22 years). I've done a
little work creating some Oracle forms (for about 6 months, and it was 8
years ago). I've also done a couple of years of VB work, about 6 years ago.
So I can probably fumble through with some fairly technical information, but
some pointers in the right direction would make things a lot easier.

Hi Craig,

Thanks for the great posts. I really appreciate the help. I'll take a look
at your comments tonight and get back to you tonight with answers to your
questions.

Chris

Ok here is some more

[quoted text clipped - 89 lines]

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

 

[cwyse via AccessMonster.com]

I thought about using lat/lon for my locations, and actually have columns for
it. However, they won't be keys, since the lat/lon is too specific. For
instance, at the same street address, there would be several lat/lon values,
but I want a single location for a street address.

As for photos, that's probably a good idea. I'll add some columns for it.

FYI... I think in my last post I said I'd put up the database for comments
again, once I get further on the forms. I'm still working on them... I've
got the first one complete, and I'm getting there on the second one. I still
have a ways to go - I probably only work on it a couple of hours per week.

cwyse,
have you thought about tagging your photos with lat,lon. I came across a
free program called 'Geosetter' ( http://www.geosetter.de/en/ ) where you
can manually set the lattitude an longitude toi a photo using a map. One
could possibably put that info into a table that could eventually be used on
google earth. If you are using a GPS with tracking abilities it can do it
automatically. but my mind wanders.

best of luck.

Hi,

[quoted text clipped - 66 lines]