Database subtyping/supertyping

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…

  • Why am I getting the following error when using database mail -> Procedure sysmail_verify_profile_sp, profile name is not valid?
  • How to insert record in existing column using SQL Server
  • Synonyms or Aliases Won't Allow Us To Schema Bind or Index a View
  • Are SQL Server timeouts logged?
  • SQLServerException: The index 1 is out of range when executiong Spring Data findAll(Pageable)
  • MSSQL and PHP: “An invalid parameter was passed to sqlsrv_query.”
  • 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, 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.

    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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.