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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Birding Life List design
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 |
#2
|
|||
|
|||
Birding Life List design
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 "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 |
#3
|
|||
|
|||
Birding Life List design
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 |
#4
|
|||
|
|||
Birding Life List design
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 CraigH wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200909/1 |
#5
|
|||
|
|||
Birding Life List design
On Fri, 4 Sep 2009 05:22:10 -0700, CraigH
wrote: 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. -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Birding Life List design
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. cwyse wrote: 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 -- Message posted via http://www.accessmonster.com |
#7
|
|||
|
|||
Birding Life List design
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. "cwyse via AccessMonster.com" wrote: 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. cwyse wrote: 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 -- Message posted via http://www.accessmonster.com |
#8
|
|||
|
|||
Birding Life List design
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. cjwagner wrote: 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] http://members.cox.net/chriswyse/BirdingDatabase.mdb -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200911/1 |
Thread Tools | |
Display Modes | |
|
|