I have tables “Crop”, “Corn”, “Soybean”, and “Grain”. One entry in Crop corresponds to a single entry in one of the other tables. The problem here is that Crop should be one-to-one with only one of the other tables, but not more than one. The Crop table is needed because it combines a lot of the common data from the other tables and makes querying the information much easier code side. From working on this I have a couple strategies with drawbacks…
A. Put three columns into Crop for the IDs of the other tables then populate the column “Corn” if it’s a corn crop ect…
Drawbacks: Wasted columns, have to check all three columns whenever I want to see what crop it is
B. Combine Corn, Soybean, and Grain tables and add a single column for what type of crop it is.
Drawbacks: Each table has different columns, wasted and unnecessary columns in each row
Is it safe to say I’m stuck here? Or is there a strategy to handle cases like this? Thanks.
2 Solutions collect form web for “Database subtyping/supertyping”
This is the “subtype” situation and is covered extensively in Stephane Faroult’s the Art of SQL
The recommended solution involves using the same unique key (in this case, say
CropID) across all tables,
Grain. The set of primary keys of the
Crop table then becomes the union of primary keys of
Grain. In addition, you define an attribute, say
CropType, on the
Crop table indicating the type of each
This way, common attributes stay on the
Crop table and type-specific attributes go to type-specific tables with no redundancy.
Why not a PivotTable for all tables like :
PivotTable -> PivotID, PivotDate Crop->CropID, PivotID, other fields Soybean->SoybeanID, PivotID, other fields Gran->GrainID, PivotID, other fields
So, you could select all tables with only one PivotID