Composite key or autonumber PK

[Liam87]

I have a monitoring database, the site_ID, Date and survey_type are indexed as this uniquely identifies each survey. I am not sure if how I really link the Site table to the other three, or if it would be better if I had a Survey_ID field that acted as the link. This then gives me a few problems in how to enter the data.(Note. I havent included PK's for the conditions, frog and bird survey as yet as I am not sure which is the best way to go at this point).
Site
Site_ID
Date_SurvSurvey_Type(i.e. frog, bird)
Conditions
Temp
Cloud_Cov
Habitat_Desc
Frog_Surv
Species
quantity


Bird_Surv
Species
Qty

Thanks if anyone can shed some light.

[June7]

If the fields in the species survey tables are all the same then use one table. Each site will have only one survey? Consider:

Species
SpeciesID
CommonName
Species
Genus
Family
Order
Class
Phylum
Kingdom

Site
SiteID
DateSurvey
Temp
CloudCov
Habitat

SiteSpeciesData
SiteID
SpeciesID
Quantity

[Liam87]

Each site will be surveyed every one-three years and it is possible that one site may be surveyed on the same day as another surveyed(surveys outsourced to contractors) that is why I had the Survey_Type field, because it will not only tell you that it is a frog survey, but also differentiate between frog-point survery and frog-transect survey. So i still need that in.

Is there a way in which you can search the species if I used that format in a data entry form?

[June7]

Okay, consider:

Species
SpeciesID (PK)
CommonName
Species
Genus
Family
Order
Class
Phylum
Kingdom

Site
SiteID (PK)
SiteName
Lat
Long

Survey
SurveyID (PK)
SiteID (FK)
DateSurvey
Temp
CloudCov
Habitat

SurveySpeciesData
SurveyID (FK)
SpeciesID (FK)
Quantity

This assumes each survey event will encompass multiple species. Will the Type apply to all species in that survey event? If so put Type in the Survey table, else in SurveySpeciesData.

[Liam87]

Thanks June7 for your help, appreciate it.