I am asking for advice on whether I am doing something in the correct way. Drop-down menus and selectable menus are a big part of this particular application. What I have works, which is great, but I’m not sure if I have got there in the right way.
In simple terms I have a database with numerous tables and there are basically three types of table:
- Content Tables - these hold pre-populated information such as Clients, Contacts, Suppliers
- Evolving Tables - these get populated daily with Jobs, Orders, Reports
- List Tables - these hold selectable information used primarily in drop-down menus such as Active (ie active/inactive), Status (ie new/processing/on hold/awaiting client/suspended/cancelled), as well as Sec Level, Category, Payment Terms etc
I currently have a table of about 40 different menus (tbl_menu) and then another table of about 200 menu items (tbl_menu_list) that belong to the menus and are connected by foreign keys. And so in this example of seclevel I have the following in the tbl_menu…
menu_id : menu_label
30 : Active
31 : Sec Level
32 : Tyre Depths
etc
and the menu items for 31 : Sec Level stored in tbl_menu_list are as follows…
menu_item_id : menu_id : menu_item_label (this table also has Order and Active/Inactive)
156 : 31 : Company
157 : 31 : Engineer
158 : 31 : Client
159 : 31 : Staff
160 : 31 : Spare
161 : 31 : Inactive
So this means that I only have 2 tables instead of 40. Surely this is more efficient. It also means one query with a join gives me access to all the menu items that I may need on a page.
QUESTION 1
- Is this the best way to set up the menus?
- Is this better than one table?
- Or should I consider 40 separate tables?
This then leads me on to how I use this information. Again, I am trying to simplify things so here I have broken down the database tables to a table of
tbl_login_users
and a table for the sec level
tbl_sec_level
And so here the update form for User Login ID 1 “James Brown” is shown selecting the Sec Level 2 “Engineer”.
And once the submit button is pressed we can see that the selection has changed to list_sec_level_id 2 “Engineer” on the summary page.
This is how the code looks behind the summary page
And this is the Dynamic Attributes > Inner Text > Value > Data Binding > Data Formats screen looks like. I got here by a bit of trial and error and by looking at the code above and using my basic coding experience to tell me what I needed.
QUESTION 2
How should I be doing this?
Although this works, I am not convinced that this is the way I should do it to achieve the right results every time. I need to get this right now as I will be doing hundreds of these over the next few weeks.
In the two screenshots above (update form and summary page) this step will need repeating for Status, Active and User so I end up with something like this rather than a list of ID numbers…
Hope this makes sense and apologies for the longest post in Wappler’s short history 
Last updated: