How to design this RDBMS schema?

Question

So we have foods, which are usually of one single unit and recipes, which consist of varying amount of different foods.

We want to track a few basic things like the price and the nutrition info.

How would we design RDBMS schema for this?

So, here is the probably most obvious solution:

[Foods] : ID, Name, Carbs, Protein, Fat, Price
[RecipeIngredients] : ID, FoodID, RecipeID, AmountOfFood
[Recipes] : ID, Name

However, since recipe has almost all the same actions as foods, I need to create a lot of duplicate code to support both.

Here is another approach:

[Foods] : ID, Name, Carbs, Protein, Fat, Price, IsRecipe
[RecipeFoods] : ID, FoodID, RecipeID (which points to another Food), AmountOfFood

Now all the actions work same for both. However, adding fields only specific to Food or Recipe creates empty columns for the other entity.

How would you solve this? Which one do you prefer? Is there a better way?

Answer 1

Keep your database normalized.

A Recipe is not a Food as you had already noticed, so they shouldn't share a table.

Each entity should have its own table in the database.

Answer 2

If you must have this database structure (+1 to Oded), make sure the unused columns are NULL and that your code handles this.