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…

  • SQL Server 2008 R2 Add column into a specific location
  • Printing the current value and previous value between the date range
  • sql server 2008 database merging with another database
  • Is there any way to view the last run sql query in sql server managment studio
  • Insert manually into a table by SQL statement, but key is autoincremented
  • How to add additional column with default value in SQL query which actually doesn't exist in the table
  • 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.