469,962 Members | 2,322 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,962 developers. It's quick & easy.

Application Design, <SELECT> Multiple or Single Table?

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
0 2010

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Felix Natter | last post: by
4 posts views Thread by headware | last post: by
5 posts views Thread by Brian Foley | last post: by
4 posts views Thread by VK | last post: by
5 posts views Thread by Sonnich | last post: by
1 post views Thread by rainxy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.