Table Design

Posted on 2014-08-04
Last Modified: 2014-08-05
I am designing a food database. The purpose of the database is to maintain the data used to track the dates on which meals, snacks, etc. were made. A snack can be a left over meal plus a single ingredient. I only need help with some table names. I am not working on the field names yet. Please comment on the proposed tables.

Here are the tables:
Question by:Mark01
    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    That's not really much to go on. From your writeup, the only real entities we know are Meals, Snacks, and Ingredients, and that a Snack could possibly consist of a Meal plus one or more Ingredients. From that, we could derive the following as a start:


    Meal_Ingredient would be a Join table to associate all the Ingredients needed for a Meal, assuming (of course) that a Meal consists entirely of Ingredients.

    Since a Snack could consist of a Meal plus one or more Ingredient:


    Snack_Items is probably a poor choice for this table name, and I'm sure you could come up with something better. The gist of this would be that a Snack would consist of several "items", and one of those could be a Meal - so do you link to a record from the Meal table, or do you simply list out the Ingredients for a Snack (which would, essentially, duplicate a Meal and add one or more Ingredients to it).

    But again, I think you need to flesh out your overall idea a bit more before you can get to the point of table design.

    Also, you mentioned Book and Recipe, but there's no information regarding how those fit into your design, so we cannot include them.
    LVL 68

    Accepted Solution

    Technically you should be dealing with "entities" and not "tables" at this point, since you should be in the logical design phase rather than the physical design phase.  That is more than just semantics.  It's integral and fundamental to a proper design.

    Single or plural names is up to you, but be consistent.  Either all singular or all plural, not a mix.

    Also, be consistent on other naming when possible.  For example:


    but only if that is an accurate reflection of the actual data, of course.

    Author Closing Comment

    Thank you, Scott McDaniel and ScottPletcher.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    A snack can be a left over meal plus a single ingredient.
    In general "food service" terms, this is to broad...

    If a snack can be a "Meal" + an ingredient, ...then it is really a "meal".
    (or are we talking about partial or leftover meals?)

    How can you call a full meal (Meat, Veg, Starch) plus another ingredient (Ice cream, ...for example), ...a "Snack"?

    As the two experts above ave stated, really need to iron all of this out first.

    A snack can be a left over meal plus a single ingredient.
    ...and what else can a snack be...?
    You need to clearly define what a Meal, snack, ingredient is:
    A Meal?
    A meal + an ingredient?
    an ingredient alone?
    Up to two ingredients?
    What about things like a bag of potato chip? (since this is not a meal, or an ingredient, it still a "snack")

    You need to clearly define what each entity is and if it can contain other entities...

    So perhaps you could give us more detail please?



    Author Comment

    Jeff, I appreciate your comments. My statement about a snack being composed of a meal plus an ingredient was  incorrect. I meant that a snack can be composed of a quantity of a leftover recipe plus another ingredient. I am not explaining this well so here is an example. A snack can be 2 cups of fruit salad and 1 tablespoon of ice cream.

    My goal of this question was to check to see if I was on the right track with the early formation of the entities. The question was answered in 20 minutes. I was surprised that I received excellent answers so quickly.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    ...ok great...

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    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…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    746 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