locked
Best table design for cascading drop down list values RRS feed

  • Question

  • Hello,

    I'm not sure if this is the correct forum but I have a SQL table design question for a .net application I'm working on.  I have a web page which contains multiple drop down list.  When a user makes a selection from one list, the next drop down list is populated with values that are associated with the value selected in the first list.  I will need to repeat this sort of logic for the rest of the drop down list on the page.  I'm not sure about the best table design for this type of functionality.  Should I create 4 or 5 tables that contains values for each separate list and use an ID to link it back to the parent drop down list?  Or should I create one large table and use a column to identify each drop down list?  Any advice on how to approach this is greatly appreciated.

    Ex 1

    Table 1(list1)                     Table 2(list2)             Table 3(list 3)

    ID Description                     ID Description             ID Description 

    Ex 2

    Table

    ID  Description  Parent_list_ID  List_ID

     

    Thursday, January 29, 2015 2:54 PM

Answers

  • If you know the relationships among those tables (T1, T2, T3 and T4), I would suggest to keep ID and Description in those tables and then create the relational tables, i.e., T1_T2, T2_T3 and T3_T4. This way you can easily display the following dropdown lists from the tables when a specific option is selected.

    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by Eric__Zhang Tuesday, February 10, 2015 1:50 AM
    Thursday, January 29, 2015 9:17 PM

All replies

  • Typically neither... Ideally you'd simply use a distinct list of values directly from the data tables. For example, if I wanted to do a department/employee cascade., I'd create a proc that generates a list of distinct departments directly from the Departments table and separate (but similar) proc that pulls employees from the Employees table (filtered by DepartmentID).

    That way, when new departments or new users get added my pick lists are automatically updated. There's no need to try and maintain a separate table (or tables) every time the base tables get updated.

    HTH,

    Jason


    Jason Long

    Thursday, January 29, 2015 3:05 PM
  • I'm not sure If I understand, it seems like I would still have to maintain four tables.  I'm going to have four drop down list which store code values.  For example, If I need to add a new code value to the third drop down list, I would still have to add the new value to the table which stores the codes for the third drop down list.  Also, I will need to be able to report off of what options have been selected.  So, instead of DepartmentID in your example, I would use Code1_ID, Code2_ID, etc.. in order to associate the values with previous drop down list selections.
    Thursday, January 29, 2015 3:28 PM
  • If you can describe what it is you're actually trying to do and the tables you currently have to work with I can do a better job of helping you.

    The reality is that cascading lists of values are pretty simple. Trying to maintain a separate set of "LOV Tables" is a pain in the neck and all around bad data design.


    Jason Long

    Thursday, January 29, 2015 4:29 PM
  • Yes, I agree.   I currently have one table that stores a list of codes in a drop down list.  To populate the list I simply select distinct from the code table.  I've been asked to 3 additional code levels to the application.  As a result, I have to add 3 additional drop down list to the application.  Each list value is associated with a value from previous drop down list.  For example, when I select a value from the first drop down list, it should populate a list of related values in the second drop down list.  When I select an option from the second list, it should populate a list of related values in the third list... I'm trying to determine the best way to store the relation between the values in each drop down list.  I will have to be able to report on the selections that have been saved at some point.  Does this make sense? 
    • Edited by Brian205 Thursday, January 29, 2015 8:45 PM
    Thursday, January 29, 2015 8:39 PM
  • If you know the relationships among those tables (T1, T2, T3 and T4), I would suggest to keep ID and Description in those tables and then create the relational tables, i.e., T1_T2, T2_T3 and T3_T4. This way you can easily display the following dropdown lists from the tables when a specific option is selected.

    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by Eric__Zhang Tuesday, February 10, 2015 1:50 AM
    Thursday, January 29, 2015 9:17 PM