0

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?

philipxy
  • 14,867
  • 6
  • 39
  • 83
jamesSampica
  • 12,230
  • 3
  • 63
  • 85
  • This is what views are for. Define a view for each of the different ways you want to retrieve the data, no duplication or waste required. – Jesse Jun 07 '13 at 23:57
  • @Jesse I disagree. When you want to be able to have an FK constraint that can be bound to only one of the types, the only solution is the supertype/subtype pattern as given in 1_CR's answer. Views won't do the trick. – ErikE Jun 08 '13 at 00:37

2 Answers2

2

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.

iruvar
  • 22,736
  • 7
  • 53
  • 82
  • 1
    Unless you also put the `CropTypeID` into the subtype tables, you can get out-of-sync data. Adding that column also lets you use a composite FK to the supertype table on `CropID, CropTypeID` which is what physically enforces that you can't insert the same `CropID` into more than one of the subtype tables. I also recommend calling it `CornID`, `SoyBeanID`, and `GrainID` in the subtype tables--because then in other tables using FKs, you use `CropID` when FKing to the supertype, and the `{Subtype}ID` when FKing to one of the subtypes. – ErikE Jun 08 '13 at 00:40
  • Awesome. I knew someone else would had to have been in this situation. Even picked up some new vocab. I've renamed the topic to reflect that and make sure others can see your answer. – jamesSampica Jun 08 '13 at 01:10
  • 2
    This pattern is also described in the info tab of [Tag:class-table-inheritance] – Walter Mitty Jun 08 '13 at 12:14
0

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

Edper
  • 9,144
  • 1
  • 27
  • 46
  • You might consider re-reading the requirements--I don't think this meets them. – ErikE Jun 08 '13 at 00:41
  • Thank you for your comment @ErikE. But I don't think this is far from the solution accepted except in my case I have a separate table that would handle all the subtypes whereas the accepted has it on one of the table, namely, Crop. Besides this could easily adopt to your recommendation wherein all FK of subtype tables could be in one table so as if you want to focus on one subtype you could do so. Although in my case again I could put it on my PivotTable. And by the way this is the beauty of SO you take the risk of presenting your solution(s)... – Edper Jun 08 '13 at 01:44
  • ...and the best one is normally accepted and mine does not happen to be one. Besides I normally see answers that are presented as starters and edited accordingly to either enhance or adopt to expanded requirements. – Edper Jun 08 '13 at 01:45