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
.
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.

Re: Application Design, <SELECT> Multiple or Single Table?

Use option 2, always. In option 1 you are combining completely differnt
things in the table, breaking most known rules of database design.
Arbitrarily using the bad design for tables with less than 10 items is
silly. Say you start with 8 items and then add three, the whole database
and user interface would change, very bad. And why would you want to code,
test and maintain two ways of doing the same thing?


wrote in message
news:1131674043.037931.283900@g44g2000cwa.googlegroups.com.. .
> 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
> .
> 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.
>