Apologies for the potentially confusing question title. I struggled to make it generic!

I am designing a wildlife recording database. An observer goes to a site and records the occurences of species on a date as part of a sample. You can have lots of samples over time at the same site. So a sample has an id, a siteid and a date etc. Each sample has a number of occurrences, which are the individual wildlife records - the species and (say) the number counted. A site is divided up in to several locations within the site (locations have a siteid) and the occurrences reference these locations - not the site directly. So occurrences have a sampleid, speciesid and locationid.

So occurrences reference both a sample and a location that need to belong to the same site for the data to be consistent. The only way I could ensure that all occurrences reference locations from the same site as the sample was to have both sample and location primary keys include the siteid and then occurrences to include siteid in its foreign key to them. Here's a screenshot below.

wildlife database schema

I have never needed to use compound primary keys in tables before and it jars a bit that occurrence has a siteID in it as part of the foreign key to both location and sample. Is this the correct way to constrain the locations and samples that occurrence can reference? Or is there an alternative table design that doesn't need compound keys? I don't have anything against compound keys if they're necessary. Are these ones necessary?

  • Wouldn't Site -> Location -> Sample -> Occurrence work?
    – Hannah Vernon
    Jun 10, 2014 at 13:26
  • There can be more than one location used within a sample via the occurrences. But the locations for that sample all need to be in the same site.
    – andyb
    Jun 10, 2014 at 13:29
  • Can Samples have only a single location, or vice-versa?
    – Hannah Vernon
    Jun 10, 2014 at 14:14
  • No. Samples can have many locations (indirectly via occurrences) but they should all be the same site as the sample. For example, say you are visiting a nature reserve with lots of ponds in it. The site is the nature reserve, the locations are each pond, and you can record occurrences of, say, frogs at any pond within the reserve as part of one sample.
    – andyb
    Jun 10, 2014 at 14:19
  • Your design seems fine. See a similar question: Constrains relationship Jun 10, 2014 at 16:02


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.