Best practice to manage dropdown data fetched from database.

What is best way to manage dropdown data from database?

1) create a table for each dropdown

or

2) 1 table for all dropdowns.

Also please consider relations between tables, for example Country, States and Cities.

And with consideration of scalability, like more dropdowns or more dropdown labels.

  Discussion posts and replies are publicly visible

Parents
  • Certified Senior Developer

    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

Reply
  • Certified Senior Developer

    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

Children