Filter ingredients choices for recipe database by category, or dynamic search

database-design

Requirement: There are thousands of ingredients in given commercial kitchen.
To simplify user interface, when entering a new recipe, only ingredients relevant to the Food Category selected by user should be shown.

For example, when entering a new cookie recipe in a form, show only ingredients used in generally used in Cookies recipes

Simple Solution (?):
A RECIPE belongs to one FOOD_CATEGORY. For example: Peanut Butter Cookie —> Cookies
INGREDIENT <- ManyToMany -> FOOD_CATEGORY

Will this suffice to meet the above requirement, and are there any "gotchas"?

Perhaps a simpler solution is to use a Ajax style dynamic search box that filters all ingredients as you begin to type.

More broadly, is there a methodology that drives Database design from the UI to prevent any potential surprises?

For example, after my initial design, I realized a MultiSelectBox with too many ingredients simply wouldn't work.

Best Answer

That data model seems right for meeting the requirements you listed.

Your other questions are more about UI than about data modeling. Yes, a dynamic search box could work and might be the best solution here, especially since you don't want to make it impossible to use an unusual ingredient in a recipe. (Some of the best recipes are made with unusual ingredients!)

Regarding “driving database design from the UI”: My opinion is that you start with product and UI design, and then you build whatever database is needed to implement that. If you don't know exactly what you're trying to build as your first iteration, you're going to have a hard time with the database. But don't build more than you need for that first iteration. You can always migrate the database to a new schema later, when your requirements change.

Related Solutions

Food Database Design – Most Efficient Way to Store Ingredients

I think ingredients should go in a master table and another table to map ingredient to recipe. The following is the basic idea:

Create table recipe
(
    recipe_id int not null,
    recipe_name varchar2(50),
    constraint pk_recipe primary key (recipe_id)
);
Create table ingredient
(
    ingredient_id int not null,
    ingredient_name varchar2(50),
    constraint pk_ingredient primary key (ingredient_id)
);
Create table food_ingredient
(
    fk_recipe int not null,
    fk_ingredient int not null,
    measure number,
    unit_of_measurement varchar2(10)
);

Please note that the script above might have some errors due to syntax.

The advantage of this approach:

Finding food or recipe with similar ingredients
Finding food or recipe with a specific ingredient
Convert measurement, well you should incorporate some more tables to store unit of measurement and conversions. You've got the idea

Hopefully this would help.

Dynamic contacts database

Check out relational database design. It's an art and a science, and something that really can't be explained to the fullest in a post. But what you should do is create relationships between contacts and all possible data that will be related to it. This is most prevalent through the use of primary and foreign key relationships.

Best Answer

Related Solutions

Food Database Design – Most Efficient Way to Store Ingredients

Dynamic contacts database

Related Question