Solved

Multiple Date Planning Entities for Food Database

Posted on 2015-01-07
8
210 Views
Last Modified: 2015-01-08
This question involves the dish planning date entities for a food database. Three dates are used for dish planning. The first date (month and year) is for longer-term planning. The second date is the planned date on which the dish is expected to be made. The third date is the actual date that the dish was made on.

Should there be three entities (Planned Dishes, Planned Dish Dates and Actual Dish Dates)?

Notice in the sample report that the Ingredient Comment changed from “another kind of bean“ on the planned date to “black beans” on the actual date.

SAMPLE REPORT

Early Planning
Date: 2-15-2015
Meal: Dinner
Type: Soup
Subtype: Vegetables with chicken
Dish: Chicken and Barley Soup
Ingredient Comment: Substitute canned red kidney beans with another kind of bean.

Actual Date Dish Made On
Date: 2-16-2015
Meal: Dinner
Type: Soup
Subtype: Vegetables with chicken
Dish: Chicken and Barley Soup
Ingredient Comment: Substituted canned red kidney beans with black beans.


PROPOSED ENTITIES

Entity: Planned Dishes
Description: The dishes that are being planned to make. This information helps the cook plan ahead, even months in advance.

Attributes:
Dish ID -- An arbitrary unique sequential number assigned to every dish.
Meal ID -- An arbitrary unique sequential number assigned to every meal.
Month and Year -- The planned month and year that the dish will be made.
Ingredient Comment ID -- An arbitrary unique sequential number assigned to every ingredient comment.

Entity: Planned Dish Dates
Description: The date on which the dish being planned for will be made. This information helps the cook plan to make a particular dish on a given date.

Attributes:
Dish ID -- An arbitrary unique sequential number assigned to every dish.
Date -- The date on which the dish is being planned to be made.
Meal ID -- An arbitrary unique sequential number assigned to every meal.
Ingredient Comment ID -- An arbitrary unique sequential number assigned to every ingredient comment.

Entity: Actual Dish Dates
Description: The actual date on which a particular dish was made.

Attributes:
Dish ID -- An arbitrary unique sequential number assigned to every dish.
Date -- The date on which the dish was made.
Meal ID -- An arbitrary unique sequential number assigned to every meal.
Ingredient Comment ID -- An arbitrary unique sequential number assigned to every ingredient comment.


SAMPLE DATA

The following sample entity data was pasted from an Excel spreadsheet.

Planned Dishes                                                
                                                
Dish                              Date      Meal            Ingredient Comment
Lazy Man's Lemon Sole                              Jan. 2015      Dinner            Use home-baked apple slices instead of canned apples
Baked Macaroni                              Feb. 2015      Dinner            Substitute canned red kidney beans with another kind of bean.
Chicken and Barley Soup                              Mar. 2015      Dinner            Calls for  frozen broccoli. Use canned mixed vegetables instead.
                                                
                                                
Planned Dishes Dates                                                
                                                
Dish                              Date      Meal            Ingredient Comment
Lazy Man's Lemon Sole                              1/14/2015      Dinner            Use home-baked apple slices instead of canned apples
Baked Macaroni                              2/20/2015      Dinner            Substitute canned red kidney beans with another kind of bean.
Chicken and Barley Soup                              3/5/2015      Dinner            Calls for  frozen broccoli. Use canned mixed vegetables instead.
                                                
                                                
Actual Dish Dates                                                
                                                
Dish                              Date      Meal            Ingredient Comment
Lazy Man's Lemon Sole                              1/17/2015      Dinner            Use home-baked apple slices instead of canned apples
Baked Macaroni                              2/20/2015      Dinner            Substitute canned red kidney beans with another kind of bean.
Chicken and Barley Soup                              3/15/2015      Dinner            Calls for  frozen broccoli. Use canned mixed vegetables instead.

The purpose of the data pasted above is to help define the entities by filling them with sample data. The Excel file containing the sample data is attached.
SampleData.xlsx
0
Comment
Question by:Mark01
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 200 total points
ID: 40536467
Should there be three entities (Planned Dishes, Planned Dish Dates and Actual Dish Dates)?
The question is unclear as you are not saying where these entities are located.
But as I see this, the dish is a separate entity than the two "Dates"
So to me, there are two "Date" entities, ...and one dish entity (in the very narrow scope of how I can address this question)

Without going too deep into your design, ...I would make the Panned date a full date and not just the month and year.
Storing month/year in the same field can cause problems as to how that value will be interpreted.
(Just enter the planned dates as the first of the month)

To me it is the "Meal/Dish" that should be tracked by date, ...not just the dish.
A Meal/Dish combination is what you seem to be really tracking here...
(as yu
Something like this roughly...
tblMeals
mID (PK)
mName
...

tblDishes
dID (PK)
dName
...

tblMealDish
mdID (PK)
md_mID (FK)
md_dID (FK)
mdPlannedDate
mdActualDate

Hope this helps

Finally, note that as we have said previously, ...this is too broad a design for us to give you any *exact* advice, ...as the entire scope of this application is unknown to us...

JeffCoachman
0
 

Author Comment

by:Mark01
ID: 40536628
@Jeffrey Coachman: Your comment was very helpful.

Shouldn’t the planned and actual dates be in separate entities because there will be unique Ingredient Comments associated with each type of date? A sample report is shown below.

SAMPLE REPORT

Planned Date
Date: 2-15-2015
Dish: Chicken and Barley Soup
Ingredient Comment: Substitute canned red kidney beans with another kind of bean.

Actual Date
Date: 2-16-2015
Dish: Chicken and Barley Soup
Ingredient Comment: Substituted canned red kidney beans with black beans.

Your comment:
Finally, note that as we have said previously, ...this is too broad a design for us to give you any *exact* advice, ...as the entire scope of this application is unknown to us...

My response:
How do I convey the entire scope of this application to you?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 120 total points
ID: 40536788
>>Shouldn’t the planned and actual dates be in separate entities

As we have pointed out in your previous questions:  There is no right answer when it comes to design.  If you have 5 people that take any design to 3NF, you will likely have 5 different designs.  Mainly because of different interpretations of the requirements but also because 3NF is a set of rules not LAWS.

If you want dates broken out, by all means, do it.
tblMealDish
mdID (PK)
md_mID (FK)
md_dID (FK)

tblMealDishOccurrence
mdID(PK),
occurrence_type(PK),
occurrence_date(PK),
occurrence_comments

tblOccurrenceType
OT_id,
OT_desc

there desc is: 'Planned','Actual',etc...


>>How do I convey the entire scope of this application to you?

You can't.  What you are asking is:  Design my application for me.

This is a simple Q&A site.  What you are asking is more for a consultant.  It is way out of scope for a Q&A site.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 180 total points
ID: 40536822
There seems to be a hierarchy of dates.
LongTermPlanningDate --->SpecificPlannedDate / ActualDate ---> comments

My original thought was two entities as someone else already mentioned.  The long term date and the specific dates with planned and actual being in the same table.  You could still do that but make the notes the third level.  There is a 1-1 relationship between specific and actual and a 1-m relationship between long term and specific.   I don't see a good reason to split the 1-1 relationship into two separate tables.  I would prefer to split off the comments.

A lot of design revolves around what you want out of the system.  What do you actually want to get out of this?  Will you ever care if you made a long term plan to make a dish but never actually put it on the menu?  Will you care if you put it on a menu but never actually made it?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Closing Comment

by:Mark01
ID: 40536974
Thank you, PatHartman, Jeffrey Coachman and slightwv.

@slightwv: I am not asking anyone to design an application for me. I am learning database design and I want to make sure that every database I design is normalized to the third normal form.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40536997
>>and I want to make sure that every database I design

3NF is a logical construct.  A database is a physical construct.  Although the two are very similar they are not a one-to-one correlation.

It is rare that a physical database is a 100% copy of the logical.

Let me go one step farther.  Most of the time a 'database' implies a RDBMS.

Does a logical design even need a RDBMS to be implemented?

Look around for the term 'Data Store' as opposed to 'Database'.

>>is normalized to the third normal form.

Then you are asking the wrong questions.  You need to be asking smaller questions about normalization.

Instead of:  How do I normalize a meal/food/recipe/catering/??? model?
Maybe:  How do I normalize 'People', 'Places', 'Things', etc...

Try to shoot for topics with one or two entities as opposed to 20-100 (likely what your 'food' model would end up being).

I think I posted this in one of your previous questions but if not, I'll post again.

Get to where you understand this statement:
 "The key, the whole key, and nothing but the key, so help me Codd."

Not what it describes but what it actually is trying to tell you.

Dig deeper into the meaning before you start to practice the theory.

In other words:
Instead of:  I push the gas and the car goes.  What more do I need?
Learn what causes the car to go when you push the gas.  Learn how all the parts work together to make the car go.

Once you do that, you'll not need our help and will be answering questions here on logical design!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40537157
First, read through all of slightwv's posts, ...he brings up a lot of great points and provides great analogies...

I am not asking anyone to design an application for me. I am learning database design
...in a way, ...you are...
You have posted a "design" and are asking us to analyze it...
We all understand your frustrations on this because all of us Experts here had to get past these same obstacles...

The stumbling block here is that a database of this apparent complexity is not the best project to take on to "Learn" database design.

As you can see from this, ...and your previous questions, ...your design here is far from being finalized, ...and with the myriad of expert suggestions, ...it may never be...
It may end up being "Good enough for now", ...but may need to be wholly redesigned when you realize that more functionality is needed, ...or you had overlooked an important aspect.

Once more I will state that without a full understanding of every single aspect of this system, ...you may get "inappropriate" (for lack of a better word) answers to all of your questions...

Can you take a step back and explain how you came to take on a project of this complexity, as your "Learning" tool?

I want to make sure that every database I design is normalized to the third normal form.
Don't get so hung up on the academics here in the "Learning stage". (Normal forms, Entities, Tuples, dependencies, hierarchy, ..etc)
When you do, you may lose sight of the big picture, ...
Sure your DB needs to be normalized, ...but you also need to consider things like Foreign keys, naming conversions, future expansion, scalability, ...etc

The best thing to do, IMHO, ...is to study the original Northwind sample database.
It is by no means perfect, ...but it is the best sample database around...(Again, IMHO)

So I would put this "Beast" aside and instead concentrate on smaller designs, ...until you get an intuitive feel for how database elements need to assembled into a fully functioning "application".
Either that, or partner with an experienced database professional to "teach" you these concepts.

Hope this helps some too...

JeffCoachman
0
 

Author Comment

by:Mark01
ID: 40538115
Thank you, JeffCoachman and slightwv, for your advice.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now