AnsweredAssumed Answered

One-to-One/One-to-None Table and Relationship Design

Question asked by lishmonahan on Oct 28, 2018
Latest reply on Oct 29, 2018 by keywords

I can't figure out the best to deal with one-to-one/one-to-none relationships. Specifically I have some 400+ archaeological sites: they're housed in my Sites_Master table. And I'm comparing the data from multiple archaeological surveys: the list of surveys is housed in my Surveys_Master table. Each archaeological site is recorded in at least one archaeological survey, sometimes many. All surveys record more than one site. And each survey collected different kinds of data, in different formats, so I've got a different data table for each survey, e.g. CS_Survey, AR_Survey, ASSP_Survey, etc. which will have a record for each site that appears in that survey.

 

So to deal with the many-to-many relationship between Sites and Surveys, I've got a join table called Sites_Surveys, which now lets me see which sites appear in which surveys (or which surveys recorded which sites). So far, so good. But what I can't figure out is how to join the actual survey data housed in the separate survey tables, since each site appears in a survey at most once, and sometimes not at all. Would I set up a one-to-one relationship between the Site_Survey table and each of the individual survey tables, using the Site_Survey_ID as the parent key, or would I use the parent key from each individual survey? Or do I make a whole series of join tables, one for each individual survey, that link the Site_Master to the survey tables.

 

The end result is that it should be some sort of conditional lookup, so if the "Site A" appears in "CS Survey" then I'm able to connect to the data for that Site in the appropriate Survey Table, as well as get the information about that particular survey. I'm sure I'm making this more complicated than it is!

 

FMPStructure.JPG

Outcomes