Relationship Table Help

[Nick F]

Hello,
I know the importance of building a strong relationship table. Unfortunately, I am really new at this have hit a mind/road block. I have posted an image of what I have so far for entering stream fish data. I start at the stream level working down to a fish survey. A fish survey samples fish at various locations on a stream. At each location, one or more sampling techniques may be employed. Each sampling technique has its unique set of information specific to that gear. The actual data collected for each gear is the for the most part is the same so the same table could be used for each sampling technique. It is important in our report writing to know what technique was used to catch fish. Is there a way to relate those tables to each technique or do I have to replicate those suite of tables for each sampling technique?

Thanks
Nick

[June7]

I don't see any relationship between Stream_Sampling and any of the tables on the right side. Is that where you are stumped? Looks like SamplingCondID, DischargeID, CrewID, StationMeasureID need foreign key fields for Stream_Sampling. Actually, if these are 1-to-1 with Stream_Sampling and every field will have data, just put all the fields in Stream_Sampling

Need a relationship between Stream_Sampling and fish sampled. Will there be more than one species per site? What are Fish_Subsample and PCA_Sampling tables for?

Crew table does not look normalized. Multiple similar name fields is indicator of that. How would you select all records that Joe crewed if could be in any of 6 fields? Would have to include all 6 fields in criteria with OR operator.

[Nick F]

Hello,

Yep. I'm stuck in developing the relationships. Maybe an example would help.

We sampled the French River in 2011 at two different spots. Sample site one is located at River Mile 15.1. We sampled it on June 15 with electrofishing with four crew members. We took data on sampling conditions, discharge, and station measurements. We caught 600 fish. 50 brown trout which were entered on the individual fish sheet. 450 brook trout which 50 were entered on the individual fish sheet. The other 400 brook trout were sub-sampled (Fish_Subsample which is a total count and total weight of the fish that are not measured). We also caught three non-game species: golden shiners, blacknose dace, and fathead minnow. The information for these species was entered on the PCA_Sampling sheet (PCA sampling is just count of the total number of fish, length and weight of the smallest, length and weight of the largest, and a total weight of all the individuals. So there would be three records for this example) . Site 1 was also sampled on June 20 with two fyke nets (two crew members). Sampling conditions data was taken for each net (No discharge or station measurements were taken). 5 brown trout and 5 brook trout were caught in the first net with data entered on the individual fish data sheet. In the second fyke net 5 smallmouth bass (individual fish data sheet), 3 brown trout (individual fish data sheet), 50 golden shiners (PCA sampling data sheet), and 75 blacknose dace (PCA sampling data sheet) were caught. Sample site two is located at River Mile 7.8. We sampled it on June 17 with electrofishing with four crew members. We took data on sampling conditions, discharge, and station measurements. We caught 400 fish. 50 brown trout which were entered on the individual fish sheet. 350 brook trout which 50 were entered on the individual fish sheet. The other 300 brook trout were sub-sampled (Fish_Subsample). We also caught two non-game species: 75 golden shiners and 30 blacknose dace. The information for these species was entered on the PCA_Sampling sheet.

This is an example of the data we collect. We write reports at the stream level. We report catch data by gear (i.e. electrofishing = # caught per minute on time OR # caught per 1,000 ft of stream; fyke net = # caught per net; etc.). We also document total number of species caught throughout the stream. Size ranges, growth rates, ages, etc. stream wide. We do compare specific sites from year to year sometimes. We also use site specific data as a reference for nearby projects.

We also put out thermometers that continously record the temperature (hourly) for a specified amount of time (anywhere from 6 months to a year). While it is in the stream, we try to sample fish at least once at that station. So another goal of this database is to be able to tie fish and temperature data together when applicapable.

Hope this helps shed light on the project I am trying to accomplish. Thanks for your help.

Cheers
Nick

[June7]

So each site will have only the one method? One example states 2 fyke nets used. Do you care which net the fish came from?

Maybe just include SurveyID as foreign key in Individual_Fish, PCA_Sampling, Fish_Subsample tables?

[Nick F]

Each site might may have multiple methods utilized. We do care which net fish came from. We calculate catch per net. If we use multiple nets, then we report an average catch per net.

[June7]

That does complicate it. Think you will need another 'junction' table between Stream_Sampling and the method tables.

Site_Method
ID (primary key) this will now be the foreign key in the method tables instead of SurveyID and also in PCA_Sampling, Individual_Fish, Fish_Subsample
SurveyID (foreign key)
MethodType
MethodIndex

This table would have a record for each method used at one site. If you have 2 fyke nets and 1 electrofishing, there would be 3 records. You would have MethodIndex 1 and 2 for the two fyke and 1 for the one electrofishing. There would be two records in Fyke_Net and related records in PCA_Sampling, Individual_Fish, Fish_Subsample tables so you have complete stats for each net.

Is this close?