By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

Application Design, <SELECT> Multiple or Single Table?

P: n/a
Hi folks.

I need advice.

2 options, which do you think is the better option to
display/retrieve/report on the data.
Keep in mind reporting (Crystal), SQL Performance, VB Code, usability,
architecture.

Case 1: On a web page I would like to render a dropdown list
<single-select>.
Eg: Yes/No; Monday/Wednesday/Friday/Sunday; Black/Blue/White

Case 2: I need to write a query that's going to list the data with the
element names.

These values have to be stored in a DB and be easily edited.

Option 1: Have a single table with 3 columns, Field_ID, Element_Name,
Element_Value
Eg: work_days, Monday, 1
work_days, Wednesday, 3
case_color, Black, 0000000
case_color, Blue, 0000FF

Stored in 1 table called tbl_dropdown_values.
1 Form where you have to select the Form, Field then it's names and
values.

Example query to select values:
SELECT tbl_dropdown_values.Element_Name FROM tbl_days INNER JOIN
tbl_dropdown_values ON tbl_days.work_day =
tbl_dropdown_values.Element_value
WHERE tbl_dropdown_values.field_ID = 'work_days'
(I could also use a sub select)

================================================== ==============

Option 2: Have a table with 2 columns Element_Name, Element_Value for
each dropdown.
Eg: Monday, 1
Wednesday, 3
Stored in 1 table called tbl_work_days.
1 Form where you enter the name and value.

Black, 0000000
Blue, 0000FF
Stored in 1 table called tbl_case_colors.
1 Form where you enter the name and value.

Example query:
SELECT tbl_work_days.Element_Name
FROM tbl_days INNER JOIN
tbl_work_days ON tbl_days.work_day =
tbl_work_days.Element_value
Option 1 Pros: 1 Form, 1 Table
Option 1 Cons: Need to know field name when writing queries, queries
more complicated. If fields repeated then I need to add the values to
the table (eg: 3 Yes/No fields)

Option 2 Pros: Multiple forms (no big deal cos forms are autogenerated
from the SQL), Multiple tables
Option 2 Cons: Inverse of the above.

What do you recommend?

My belief is that I should use a combination of the 2. If I've got < 10
values then use a single table if I have > 10 then use a dedicated
table, but then they have to edit values in 2 places.

Thanks folks.

Nov 17 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.