How to Structure Table to Model Mutually Exclusive 1:n Relationships?

Question

In the following table structure:

Fruits 
(
    fruit_id,
    fruitName
)


Vegetables
(
    vegetable_id,
    vegetableName
)

favoriteFoods 
(
     food_id,
     foodName,
     type_id (References either a fruit or a vegetable)
)

I realize that I could forgo using a foreign key constraint on the favoriteFoods table and then simply add a type field to the favoriteFoods table to differentiate between fruits and vegetables. But how would you structure the tables so that you could actually create the necessary foreign key constraints?

Answer 1

I would only use 2 tables instead. Instead of having a separate Fruits and Vegetables table, why not have a table of Foods. Then have a foreign key constraint on fkfood_id to food_id. Then if for some reason you ever have to add meat, it would be much easier to maintain the application that uses this.

Food
    (
    food_id,
    foodName,
    foodType
    )

favoriteFoods
    (
    favoritefood_id,
    fkfood_id
    )

Answer 2

It depends on what you are going to do with the data.

If for some reason normalization is important to you, the following works quite well.

Fruits (    
     fruit_id,
     food_id references favoritefoods.food_id,
     fruitName)
Vegetables(
     vegetable_id,
     food_id references favoritefoods.food_id,
     vegetableName)
favoriteFoods (
     food_id,
     foodName)

The favoriteFoods table doesn't need to "know" what type of food it is, if any. Each fruit and each vegetable is bound to the corresponding favorite food.

If you want to select all the fruits from favoriteFoods, just join the fruits table and the favoriteFoods table. You could even include a tomato as both a vegetable and a fruit, if that suits your fancy.

The above is predicated on the assumption that joins are cheap. In many situations, joins really are cheap. Check it out before you alter your design to avoid joins.