Design issues - Check Boxes, Lookups

[Craig]

I'm re-designing a database. One of the forms in this database has 10
checkboxes - each one is bound to a separate field in the underlying table.
The user can select one or any number of checkboxes.

If you look at the underlying table, there are 10 fields - one for each of
the checkboxes on the form. Is there a better/more appropriate way to store
these results? Maybe a separate table, with a foreign key linked back to the
main table?

Also, another issue: I'm intrigued with all the threads regarding the evils
of using lookup values in tables. However, the database I'm re-designing has
many 'valuelists'. I can't say I'm a big fan of those, either. I agree with
the newsgroup postings - separate tables should be used to store lists and
then they should subsequently be managed through forms. Should these
valuelists be made into separate tables? What criteria is generally used to
determine if they should be, or remain in a valuelist?

Example of an existing valuelist: A combobox on a form has the following
interest rate groupings: 0$; 0%-4.99%; 5.00%-9.99%;10.00%-14.99%;Over 15.00%

How would the above example be simplified through a lookup table?

Thanks. And sorry for the deluge of questions....I have found these
newsgroups quite beneficial and truly appreciate all the help.

 

[Tim Ferguson]

Just my tuppence-worth:

If you look at the underlying table, there are 10 fields - one for
each of the checkboxes on the form. Is there a better/more
appropriate way to store these results? Maybe a separate table, with
a foreign key linked back to the main table?

Yes, pretty much that. Take a look at AtYourSurvey for ideas. What
happens when you need to add an eleventh flag?

Also, another issue: I'm intrigued with all the threads regarding the
evils of using lookup values in tables.

It's actually lookup fields -- specifically, the workings of the nasty
lookup field Wizard which makes a mangle and obscures the details of what
is, essentially, a perfectly straightforward one-to-many relationship.

Example of an existing valuelist: A combobox on a form has the
following interest rate groupings: 0$; 0%-4.99%;
5.00%-9.99%;10.00%-14.99%;Over 15.00%

This is dodgy territory, because it's hiding quite important database
integrity stuff in the user interface. What happens when someone creates
a new form; or wants to use Excel to manipulate the data; or when someone
needs a new category of 25.00+; and so on? In extremely limited or
trivial situations it's a short cut, but it's not very short and there
are serious side effects.

How would the above example be simplified through a lookup table?

Well, you just have a table with one field, which is also the PK, like
this (the space chars are significant if you want it to sort correctly)

PayRanges
=========
Description
===========
00.00
0.01- 4.99
5.00- 9.99
10.00-14.99
15.00 plus


and then make a Text(11) field in the target table and declare it as a
foreign key referencing PayRanges(Description). You get control over what
goes in the OtherTable(CostEstimate) field, and no need to do any joins
ever!

All the best


Tim F

 

[Craig]

Tim...thanks for your help!
--
Craig

Just my tuppence-worth:


Yes, pretty much that. Take a look at AtYourSurvey for ideas. What
happens when you need to add an eleventh flag?


It's actually lookup fields -- specifically, the workings of the nasty
lookup field Wizard which makes a mangle and obscures the details of what
is, essentially, a perfectly straightforward one-to-many relationship.


This is dodgy territory, because it's hiding quite important database
integrity stuff in the user interface. What happens when someone creates
a new form; or wants to use Excel to manipulate the data; or when someone
needs a new category of 25.00+; and so on? In extremely limited or
trivial situations it's a short cut, but it's not very short and there
are serious side effects.


Well, you just have a table with one field, which is also the PK, like
this (the space chars are significant if you want it to sort correctly)

PayRanges
=========
Description
===========
00.00
0.01- 4.99
5.00- 9.99
10.00-14.99
15.00 plus


and then make a Text(11) field in the target table and declare it as a
foreign key referencing PayRanges(Description). You get control over what
goes in the OtherTable(CostEstimate) field, and no need to do any joins
ever!

All the best


Tim F