CODEWITHSUNDEEP
database-designrdbms
randomguy
randomguy

Reputation: 12252

How to design this RDBMS schema?

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?

Upvotes: 0

Views: 1024

Answers (2)

Brian Hooper
Brian Hooper

Reputation: 22074

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

Upvotes: 0

Oded
Oded

Reputation: 499132

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.

Upvotes: 2

Related Questions