How to handle a specific type of relationship

[thebigthing313]

Hello all,

I am curious as to how to best structure a situation like this:

There are two main activities that warrant their own tables: tblInspections, tblAerialInspections.
Either of these activities may or may not generate a sample. Never more than one.

Is it best practice to have a tblSamples with two foreign keys, one for tblInspections and one for tblAerialInspections, and then either or, but never both, will have a value?
Or is it better to store a SampleID in both tables?
Or is there better method?

Thanks in advance.

[ranman256]

Cant you put both into 1 table called tblInspections and a field to differentiate
[inspType]
AERO
NORM

[orange]

You should google supertype/subtype and see if any of the associated links give you any insight on this issue.

Why, in plain English, do these warrant separate tables? They may very well, but I'd like to know how you decided that.
Do you have clear definitions of each of these terms?

[thebigthing313]

The information collected by Regular Inspections and Aerial Inspections are completely different and unrelated, but they each can generate a sample. All samples are handled the same though, and can in turn result in 0 to many analysis results.

Reading up on supertype/subtype now.

[orange]

If the 2 "things" are completely different and unrelated, then it sounds like you have separate things.

Super and sub type are meant to deal with situations such as

Employees --- we have 3 types Salaried, WeeklyWage and Casual as and when needed

or Vehicles --- Cars (passenger capacity), Trucks (number of axles), MotorCycles, Airplane

My first impression with your tables was Inspections (super), Aerial (sub), Regular(sub) BUT I don't think this applies.

[thebigthing313]

Yea, after reading up, I ended up with the same conclusion. I'm not sure on what to look up for what applies to my situation to structure this in the best way. Any further advice would be appreciated.

EDIT: Unless of course having two Samples table is the best solution, but that doesn't seem right to me since those Samples tables will have the exact same fields, with the only difference being a foreign key field linked to the PK of either tblInspections or tblAerialInspections.

[orange]

I'm not following the

completely different and unrelated

and

those Samples tables will have the exact same fields,

.

How about giving readers 4 or 5 records of each type of Inspection, and see what comments arise?

[thebigthing313]

So the fields make sense, this database is for mosquito control. Here are the involved tables:

tblInspections
PK_InspectionID, FK_InspectorID, FK_InspectionSiteID, InspectionDate, ysnIsWet, FK_BreedingDensityID, ysnPresentStage1,..2,..3,..4

tblAerialSprayZoneChecks
PK_AerialSprayZoneCheckID, FK_SprayZoneID, FK_InspectorID, InspectionDate, FK_RecommendedCourseOfAction
1:M
tblAerialInspections
PK_ASInspectionID, FK_AerialSprayZoneChecksID, Latitude, Longitude, FK_HabitatID, ysnIsWet, LarvaePerDip

Each of these activities can generate zero or one sample. My idea of how the samples tables would work:

tblSamples
PK_SampleID, FK_BiologistID, AnalyzedDate
1:M
tblSamplesResults
PK_ResultsID, FK_SampleID, FK_MosquitoSpeciesID, NumberOfLarvae

I know these are the actual fields, but if sample records would help better, let me know.
If I only make one pair of these sample tables, I am trying to find the proper way (best practice) to identify if a sample came from tblInspections or from tblAerialInspections.