CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-schemalookup-tables
enter_display_name_here
enter_display_name_here

Reputation: 698

Database table design for a customized scenario

I am trying to design a database that will store one (or more) meal plans for different people. There are 2 different scenarios in this question.

First Scenario:

(Please refer to image)enter image description here

There are 4 tables: PERSON, MEALPLAN, MEALPLAN_FOOD, and FOOD. The PERSON table stores basic information for each individual owning a meal plan. The MEALPLAN table will keep track of each meal plan (diet). The FOOD table is table of different foods (e.g. egg, spinach, sweet potato, oatmeal, etc.) and stores the protein/carb/fat/calorie information for each 'quantity unit' (e.g. 1 cup) for each food item. The MEALPLAN_FOOD table serves as the lookup/associative table for MEALPLAN and FOOD.

I believe that I have these tables set up correctly. Each person has one (or more) meal plans. Each meal plan consists of one (or more) foods/qty. My question comes in the next scenario.

Second Scenario:

(Please refer to image)enter image description here

The first scenario is limited in the fact that it only stores a list of food items per meal plan. In the second scenario, we would like to incorporate additional tables so that the meal plans can be broken down and stored by meal. Each person will one (or more) meal plans with between 1 and 4 meals per meal plan (meal plans may consists of only 2 or 3 meals). In order to accomplish this, the first scenario's MEALPLAN_FOOD lookup/associative table was replaced with 2 lookup/associative tables (MEALPLAN_M<#> and M<#>_FOOD) and a meal table (M1 for meal 1, M2, M3, and M4).

Question: Assuming that the design of the first scenario is correct, is the design of the second scenario correct in accomplishing the additional functionality? Is this design correct in additional lookup tables in order to allow for the storage of food items per meal per meal plan per person? Or, is there a better way/design for accomplishing this task?

Thank you!

Upvotes: 2

Views: 2098

Answers (1)

Dan Field
Dan Field

Reputation: 21641

Generally not a good idea to have a table per entity (i.e. a table per mealplan/meal of the day). Instead, alter your original design - add another column on the MEALPLAN table that foreign keys to a new table called MEALOFDAY. That way, you can add or remove meals of the day (allow a user to have 6 or 10 meals per day), and you can allow a meal plan to be shared between multiple meals if you add another many to many table for MEALPLAN_MEALOFDAY.

Your new table structure for MEALPLAN and MEALOFDAY migh tlook like:

MEALPLAN
========
mealplan_id (pk)
date
notes
person_id (fk)
mealofday_id (fk)

MEALOFDAY
=========
mealofday_id (pk)
meal_number
meal_description

Alternatively, get rid of that mealofday_id on MEALPLAN and create another table like so:

MEALPLAN_MEALOFDAY
=========
mealofday_id (fk/pk)
mealplan_id (fk/pk)

Upvotes: 3

Related Questions