Best practice to manage dropdown data fetched from database.

I've always seen a mixture of both approaches in real-world projects.  Country and State lists are most commonly (AFAIK) kept in their own tables since there is often other, fairly unique metadata that is / might be included with each entry. 

Then for other dropdown types, particularly things that are explicitly unique and relevant only to your application, there is often a single "reference values" table, where each entry has its own unique id, then either an ID pointing to a "reference types" table, or more lazily, just a text field with the reference type name (this is a lot easier to deal with but can be slightly less scalable in certain ways).  These often include things that might ever be applicable, like "IS_ACTIVE", and also i recommend including a column to sort by since sorting alphabetically and/or by Primary Key might sometimes not be sufficient.  I usually name this column like "ORDER_ID" but I've seen other names - it's up to you.

Hi vamsik0002 ,

I would suggest to create separate tables with  foreign key relationships. And then you can use the selected option dropdown populate the following dropdown( cascading dropdowns ), pls refer the below link for https://docs.appian.com/suite/help/21.1/recipe-configure-cascading-dropdowns.html

Ex : Country is Parent table then State and then City table. For long Run its easy to maintain.

Country 

CountryId_pk  CountryName

1                Australia

2               USA

3              Singapore

State

stateid_pk  StateName    countryid_fk 

1               Texas            2

2               California      2

3             Melbourne      1

Cityd_pk  CityName    Stateid_fk

1               Houston           1

2              Austin                1

3             Los Angeles      2

4            SanJose            2

Note: Also You can maintain single table. But above logic helps you refer/reuse wherever you need only fields. 

Please let me know if this helps. Thanks

If this reference data is fairly simple and static, then putting it into a constant is also a viable option.

Hi Baratc,

Countries, states and cities is an example.

I am looking at a real time project with 10+ dropdowns, some with 10+ choices and some with 1-2 choices.

Maintaining 10+ tables with relations is cumbersome right, and there are dropdowns which depend on multiple fields.

Hi Vamsii,

i would suggest document all the possible combinations in excel and understand the relationship between values and take the decision.(need to do some matrix like combinations and  add flags/boolean values). pls give a try and optimize to  less tables(kind of rules table).