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.