Entity Relationships Tutorial

[alaska_av8r]

First a bit of background, the last time I created a database was with a program I believe was called Lotus and that was 30 plus years ago. I am a self taught computer flunky, mostly dealing with websites but have my fingers in everything. But when it comes to Access I am a newbie!

I did a bit of reading and found the entity relationship tutorial and started working on that because I know the database I am building is all screwed up.

So using the tips in E/R Tutorial:
I am building a Deer Hunting Club database. We are what is called a Quality Deer Management club which means we have specific harvest targets and rules, we work on habitat improvement as well. To track our progress we collect a lot of data that I would like to use a database to help with analysis.

We need to track Bucks, Does, Fawns and unknowns, along with Hunters, Food Plots, Stands and Harvest Info.

For Bucks only (we only need to collect numbers for does, fawns, unknowns) we will collect age, antler points, antler spread, beam length, comments, active/inactive

For Hunters we need to track Last and First Names, phone, address, total dues, amount paid, date paid.

For food plots we need to track Location, size, crop and soil test

For stands we need to track Location, type, owner (hunter)

We also want track deer observations, this data will include Hunter, Date, Time, Location (Stand or Food Plot), Bucks, Does, Fawns, Unknowns, weather, temp, wind, baro press, moon phase and comments

We also want to track deer harvest with data that will be identical to Observations.

I am attaching a copy of where I am diagramming this out as shown in the tutorial, but I got stumped on a few things. I plan on using *somethingID as my primary keys and didn't want to get wrapped around doing multi-field keys etc.

I could NOT figure a relationship between Buck and Location (Stand or Food Plot), the only real world relationship occurs through the Observation or Harvest which will specify Location (Food Plot or Stand). The whole Location, Stand, Food Plot thing has me all confused, I will use Lat/Long for all three of them and they CAN be separate entities, BUT I could also make Stand and Food Plot as children of Location.

So I am trying to figure this out but gonna need a bit of help.

Thanks in advance for any help, I really hope this isn't over my head.

 

[stopher]

I think if you start off with three entities:
Hunter
Observation
Location

Then one hunter is related to many observations. Also, one location is related to many locations.

Next is how you deal with Stands and Food Plots. In both cases the question is how many Stands/foodplots can be at a given location? If the answer is many then you will need another entity for Stands and another entity for food plots if that is also many with location.

If Stands or food plots are one to one with location then you show it as that in your entity diagram. But in the case of one-to-one, when you come to creating your tables you have the opportunity to include extra columns in your location table to account for the stands and food plots. It's worth noting that perhaps some locations may not have either (i don't know), so you will have some locations with neither stand or food plot info filled in. This is fine. The alternative is to create separate tables for stands and food plots and have a one to one relationship with location. Your preference really.

As for the key for location, that's a tricky one. The obvious key is long/lat. If this is how you uniquely define a location in the real world then I'd probably stick with that. How easy would it then be for someone to select a location? Also, could someone enter the same location but maybe just a few metres from another location - is this ok (?)

hth

 

[chellebell1689]

Hello,
So I'm still a noob at this E/R, but I followed a tutorial jdraw sent me and going by his tutorial and what you've said, it seems like you might need to make an additional table to connect the other two. If I understand E/R, what your saying, and how the tutorial set it up. Hope it helps.
(Here's the link to my post http://www.access-programmers.co.uk/forums/showthread.php?t=276993 , jdraw's tutorial is the link in the second comment.)

 

[alaska_av8r]

Thanks for the input guys.

Stopher you addressed the confusion issue very well. Lat/long would certainly be the unique separator for location but hard for user to be able to input.

What I originally thought I wanted to be able to do is when I query, I wanted to be able to determine the number of bucks observed from stands, another query would be number of bucks observed on food plots. But since a stand could be on a food plot (same location) that has me confused.

However in reality, what I now realize that I really want to know is how many bucks were seen at a specific point on the ground (lat/long). But lat/long is not very user friendly.

So following what you mentioned, I really want to work with Location. Our property is divided into sections, sec 1, 2, 3 etc.

So to keep it simple i'll treat Locations as 3 separate types:
1. Section (just a general area)
2. Stand (not on food plot)
3. Food Plots (with or without stands)

Would that work? Using that layout will I still be able to query how many bucks were seen in each section, each stand and each food plot?

And going down that road would the attributes of tblLocation be the 3 items above, with a relationship to their own table tbl Section which would contain Sec 1, 2 ,3 then tbl Stand which would contain Stand 1, 2, 3 and finally tblFoodPlots containing Food Plot 1, 2, 3

And going further I would break down tblSection entries with their lat/long
tblStands would have type, lat/long, owner etc
tblFoodPlots would have lat/long, size, crop etc

Chellebell thanks for the info, I will look at that tonight.

tim

 

[alaska_av8r]

Okay I went through the tutorial again, its the same one I posted in the original post. I am still a bit confused but getting there. I still am not sure I'm normalized properly. I can enter a new stand with the form, enter new food plot with the form,

I'm still not sure this is the best way to do Location, I would like to just put Location as one of the fields in the tblObservations and be able to select a value from 1 of the three tables (tblSection, tblStand, tblFoodPlot) but I couldn't figure out how to do that so I had to put Stand, Food Plot and Section in the tblObservations (with a note on the form to choose only one)

Also if you look at the tblObservations you will see I have totalBucks, Buck 1 ID, Buck 2 ID, etc

Not sure the best way to handle this, we will be entering bucks into tblBucks from capturing their picture with a game camera, so on the deer observation form I don't want them being able to add bucks to tblBucks, however I either want them to be able to select them if they see them using their BuckID to record the observation (using a relationship), or I will provide a paper with buck info and they will enter the buckID. The problem I ran into was what happens when they see several bucks.....don't know what to do there.