Dynamically filling dropdown values or manually
-
I work in a small team developing a asp.net web app in C#. We have 15-20 pages (modules) that offer mostly different kinds of inventory tracking. On each page (amongst other things) there are various dropdown fields available for users to choose from. Almost none of these fields overlap so we have many different lists to manage. We're currently in a development style argument though. Right now all the dropdown fields are manually listed. Meaning, on each page, for each dropdown field, we have the list items typed out in the aspx page. For example:
<InsertItemTemplate>
<asp:DropDownList id="ddlStatusInsert" runat="server" >
<asp:ListItem Value="">Unknown</asp:ListItem>
asp:ListItemAvailable</asp:ListItem>
asp:ListItemOut of Service</asp:ListItem>
asp:ListItemClosed</asp:ListItem>
asp:ListItemLost</asp:ListItem>
</asp:DropDownList>
</InsertItemTemplate>Now, we want to create 'list managers' for these. So move the values to a separate database table and dynamically pull from the tables to populate the dropdowns. First question, I'm curious what your typical projects look like in regards to this issue. Are the dropdown values manually listed out and what do you think of that method? Second question (and the biggest source of disagreement on our team), when you do move the values out to separate tables, do you continue to store the string values in the original database tables and join to the 'list manager' tables on that string, or do you create IDs on the 'list manager' tables and do a bunch of JOINs on the IDs between the original tables and the new tables? It's being hotly debated here and I'm curious what the projects out there face in similar situations. Thanks!
-
I work in a small team developing a asp.net web app in C#. We have 15-20 pages (modules) that offer mostly different kinds of inventory tracking. On each page (amongst other things) there are various dropdown fields available for users to choose from. Almost none of these fields overlap so we have many different lists to manage. We're currently in a development style argument though. Right now all the dropdown fields are manually listed. Meaning, on each page, for each dropdown field, we have the list items typed out in the aspx page. For example:
<InsertItemTemplate>
<asp:DropDownList id="ddlStatusInsert" runat="server" >
<asp:ListItem Value="">Unknown</asp:ListItem>
asp:ListItemAvailable</asp:ListItem>
asp:ListItemOut of Service</asp:ListItem>
asp:ListItemClosed</asp:ListItem>
asp:ListItemLost</asp:ListItem>
</asp:DropDownList>
</InsertItemTemplate>Now, we want to create 'list managers' for these. So move the values to a separate database table and dynamically pull from the tables to populate the dropdowns. First question, I'm curious what your typical projects look like in regards to this issue. Are the dropdown values manually listed out and what do you think of that method? Second question (and the biggest source of disagreement on our team), when you do move the values out to separate tables, do you continue to store the string values in the original database tables and join to the 'list manager' tables on that string, or do you create IDs on the 'list manager' tables and do a bunch of JOINs on the IDs between the original tables and the new tables? It's being hotly debated here and I'm curious what the projects out there face in similar situations. Thanks!
anjelone2 wrote:
First question, I'm curious what your typical projects look like in regards to this issue. Are the dropdown values manually listed out and what do you think of that method?
No. Just because you can manually add the items doesn't mean you should. What happens if you want to offer localised versions or you want to add a new item or remove one?
anjelone2 wrote:
Second question (and the biggest source of disagreement on our team), when you do move the values out to separate tables, do you continue to store the string values in the original database tables and join to the 'list manager' tables on that string, or do you create IDs on the 'list manager' tables and do a bunch of JOINs on the IDs between the original tables and the new tables?
Why is this even being debated. Have they never heard of database normalisation? Look at the space that's being wasted in a table storing the string values for every row - store the id of the item from the lookup table and do a join instead. It's only a few extra characters. BTW - one objection I sometimes hear is that you can't delete a lookup item in future because this breaks referential integrity, and this is true - you should never delete a lookup that has been used. What you can do though is add a Deleted column which indicates whether or not the item has been deleted.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith
As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
-
anjelone2 wrote:
First question, I'm curious what your typical projects look like in regards to this issue. Are the dropdown values manually listed out and what do you think of that method?
No. Just because you can manually add the items doesn't mean you should. What happens if you want to offer localised versions or you want to add a new item or remove one?
anjelone2 wrote:
Second question (and the biggest source of disagreement on our team), when you do move the values out to separate tables, do you continue to store the string values in the original database tables and join to the 'list manager' tables on that string, or do you create IDs on the 'list manager' tables and do a bunch of JOINs on the IDs between the original tables and the new tables?
Why is this even being debated. Have they never heard of database normalisation? Look at the space that's being wasted in a table storing the string values for every row - store the id of the item from the lookup table and do a join instead. It's only a few extra characters. BTW - one objection I sometimes hear is that you can't delete a lookup item in future because this breaks referential integrity, and this is true - you should never delete a lookup that has been used. What you can do though is add a Deleted column which indicates whether or not the item has been deleted.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith
As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
To the second question, one side argues the point you make exactly. That is what relational databases are made for and allows scalability without much thought. It is also just good programming practice. The other side argues something to your BTW point. They feel that when a list value changes, there is no way to capture what the old value was (for tracking sake). That side also argues that it makes joins to the main table lengthy, especially if there are 6-10 lists to join to. That's 6-10 LEFT OUTERs on that query.
-
To the second question, one side argues the point you make exactly. That is what relational databases are made for and allows scalability without much thought. It is also just good programming practice. The other side argues something to your BTW point. They feel that when a list value changes, there is no way to capture what the old value was (for tracking sake). That side also argues that it makes joins to the main table lengthy, especially if there are 6-10 lists to join to. That's 6-10 LEFT OUTERs on that query.
anjelone2 wrote:
They feel that when a list value changes, there is no way to capture what the old value was (for tracking sake).
Of course there is. Audit your lookup data and store the old value in a history table (along with the period it applies to).
anjelone2 wrote:
That side also argues that it makes joins to the main table lengthy, especially if there are 6-10 lists to join to. That's 6-10 LEFT OUTERs on that query.
And that's just laziness. It's not a valid argument.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith
As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
-
To the second question, one side argues the point you make exactly. That is what relational databases are made for and allows scalability without much thought. It is also just good programming practice. The other side argues something to your BTW point. They feel that when a list value changes, there is no way to capture what the old value was (for tracking sake). That side also argues that it makes joins to the main table lengthy, especially if there are 6-10 lists to join to. That's 6-10 LEFT OUTERs on that query.
-
anjelone2 wrote:
First question, I'm curious what your typical projects look like in regards to this issue. Are the dropdown values manually listed out and what do you think of that method?
No. Just because you can manually add the items doesn't mean you should. What happens if you want to offer localised versions or you want to add a new item or remove one?
anjelone2 wrote:
Second question (and the biggest source of disagreement on our team), when you do move the values out to separate tables, do you continue to store the string values in the original database tables and join to the 'list manager' tables on that string, or do you create IDs on the 'list manager' tables and do a bunch of JOINs on the IDs between the original tables and the new tables?
Why is this even being debated. Have they never heard of database normalisation? Look at the space that's being wasted in a table storing the string values for every row - store the id of the item from the lookup table and do a join instead. It's only a few extra characters. BTW - one objection I sometimes hear is that you can't delete a lookup item in future because this breaks referential integrity, and this is true - you should never delete a lookup that has been used. What you can do though is add a Deleted column which indicates whether or not the item has been deleted.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith
As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
Pete O'Hanlon wrote:
add a Deleted column
Right, never* delete data. Archive perhaps, but don't delete. * At least don't make it the first thing you think of.
-
To the second question, one side argues the point you make exactly. That is what relational databases are made for and allows scalability without much thought. It is also just good programming practice. The other side argues something to your BTW point. They feel that when a list value changes, there is no way to capture what the old value was (for tracking sake). That side also argues that it makes joins to the main table lengthy, especially if there are 6-10 lists to join to. That's 6-10 LEFT OUTERs on that query.
anjelone2 wrote:
That is what relational databases are made for and allows scalability without much thought. It is also just good programming practice.
Surely this alone is enough to win the argument? The counterarguments are pretty much just rubbish, as the other posters have just pointed out:
anjelone2 wrote:
it makes joins to the main table lengthy, especially if there are 6-10 lists to join to
This is particularly offensive, anyone arguing this doesn't understand the nature of relational databases properly or is just plain idle. There is good, solid maths behind database design, if you understand this and apply it correctly your system will be much more scalable and robust. The only real problem with relational Databases is how to translate the real-world information into the abstract relational world, but all IT systems (except those modelling abstract systems themselves) inherently suffer from this problem.