Wrong Database Design

[Ralf]

Dear All,

Currently I ask myself if the design of my database is wrong.

I have several tables with a lot of fields which use 'Value List' as a row
source type. Each of my row sources look like this: ';;1;">= 1 hours
arrival";2;"< 1 hours arrival";8;"Not applicable";9;"Unknown"'. Of course
each row source is different content wise.

Now, my customer got back to me and wants to build up his own queries which
involve those kind of row sources. Because he is not that familiar with
writing SQL so he uses the designer and realised that there were no tables
for all of those values I put into 'Value List's.

1. So, my initial question is, should the usage of 'Value List's be avoided?
2. But on the other hand, is it practical to have up to 40 additional
mini-tables with the maximum of 6 rows?
3. And what would you do if you're in my situation now to fulfil the
customer's wish?

Thank you,
Ralf

 

[Duane Hookom]

I use lookup tables rather than value lists. I also avoid defining Lookup
Fields in tables. Properly normalize your tables and use combo boxes on forms
for all user input.

 

[John W. Vinson]

Dear All,

Currently I ask myself if the design of my database is wrong.

I have several tables with a lot of fields which use 'Value List' as a row
source type. Each of my row sources look like this: ';;1;">= 1 hours
arrival";2;"< 1 hours arrival";8;"Not applicable";9;"Unknown"'. Of course
each row source is different content wise.

Now, my customer got back to me and wants to build up his own queries which
involve those kind of row sources. Because he is not that familiar with
writing SQL so he uses the designer and realised that there were no tables
for all of those values I put into 'Value List's.

1. So, my initial question is, should the usage of 'Value List's be avoided?
2. But on the other hand, is it practical to have up to 40 additional
mini-tables with the maximum of 6 rows?
3. And what would you do if you're in my situation now to fulfil the
customer's wish?

Thank you,
Ralf

0. The use of Lookup Fields in tables is an abomination that should never be
used. See http://www.mvps.org/access/lookupfields.htm for a critique.

The user should NEVER see table datasheets. If the user is wearing both a
"user hat" and a "developer hat" then he should a) learn enough SQL to avoid
danger and b) only look at table datasheets when he's developing; the user
view should exclusively use Forms.

On the Forms, by all means you should use combo boxes ("lookups"); if their
data is short, static, and basically never to be edited, you can use Value
Lists (for example "Mr.";"Ms.";"Mrs.";"Miss") but as a rule, having a
sufficient number of small, editable, well defined lookup tables is perfectly
routine practice.