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

Question

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.

Answer 1

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.