I have tables Crop, Corn, Soybean, and Grain. One entry in Crop corresponds to a single entry in one of the other tables.
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.
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 etc.
Drawbacks: Wasted columns; have to check all three columns 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.
How do I handle cases like this?
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, Crop, Corn, Soybean and Grain. The set of primary keys of the Crop table then becomes the union of primary keys of Corn, SoyBean and Grain. In addition, you define an attribute, say CropType, on the Crop table indicating the type of each Crop record.
This way, common attributes stay on the Crop table and type-specific attributes go to type-specific tables with no redundancy.