Greetings,

Please see the attached image for this post, it serves as an example for my question.

What I'm Trying To Do:

Create two auto-populated drop-down boxes based on the contents of the CATEGORY and COOKINGMETHOD fields. These drop-down boxes will appear on a data entry webpage which submits its contents to the database.


3 Ways TO Achieve This (that I can think of):

1.) Break out CATEGORY and COOKINGMETHOD into their own tables then place their foreign key into RECIPE. This would allow the contents of each drop-down box to have the most accurate list of categories. It would also narrow down the possibility of a user misspelling a CATEGORY or COOKINGMETHOD, since an administrator would be in charge of adding a new CATEGORY or COOKINGMETHOD to the database.
In the current design (see attached image), if a recipe is deleted, its CATEGORY and COOKINGMETHOD are also deleted. Therefore, if Grilled Eggplant was the only desert in our recipe database, then when a user tries to enter a new desert recipe on the data entry webpage, the drop-down box will not have a desert option listed since it was deleted when Grilled Eggplant was deleted.
Database performance should be considered if going this route. Is it worth breaking these two fields out into their own tables just to satisfy the layout of the data entry page? What if there were more auto-populated drop-down boxes we wanted to place on the data entry webpage that were based on the contents of other fields? This could result in many small tables.

2.) Use the current design and auto-populate the drop-down boxes based on the values in CATEGORY and COOKINGMETHOD. Although the easiest method, it can lead to the same problem discussed in the later part of #1 (see above). This method would require an input box (so that users could create a new category if one is not listed in the drop-down box) and a drop-down box (so users could select a category if it is already listed in the drop-down box) on the data entry webpage. The chances of misspelling increase, two boxes are required instead of one, and the drop-down boxes might not always contain a category or cookingmethod for the recipe the user wants to enter.

3.) Auto-populate the drop-down boxes based on hard-coded values in the code. This would mean having to edit the code each time a new CATEGORY or COOKINGMETHOD is needed (or other value in a different field). This option makes me nervous.

I'm sure my question is pretty basic and has been answered millions of times over by more experienced developers. I would appreciate your advice.

Thank you for your time,

*Nick*

Originally Posted by nick1

Database performance should be considered if going this route. Is it worth breaking these two fields out into their own tables just to satisfy the layout of the data entry page? What if there were more auto-populated drop-down boxes we wanted to place on the data entry webpage that were based on the contents of other fields? This could result in many small tables.

You have to look at database performance holistically. The most important question is which data will be accessed most often? Sometimes many small tables are an ideal way to go. But performance takes a back seat to achieving what you want to do.

In SQL Server ( and Oracle, but that would be slightly different ) the scalable way to handle this would usually be with indexed views. But that means only data that's already in your tables can be entered. The real problem with having a second table strictly for populating the drop downs would be, well, knowing when to add the same record in both places, and knowing when it's already there.