Lookup list of "various" values in one table or multiple

[zollo9999]

Hi
My explanation is first, and Question is at the end.

My relational db design experience says the way to design lookups is to have a table for each category of lookup.

It gets a bit clumbsy with several small lookup tables each only containing 5 or so values.

I have seen a few others do it differently.
They put all the values in one table with an extra column indicating the category of the corresponding value.

ie
Category Value
status Registered
status Complete
.
.
tasktype New Applic
tasktype Closure
tasktype Transfer


The where-ever you need to lookup a list, (the row source in a table or a form combo box), you use a simple query that filters for the category of value you need so on the relevant list of values shows up.

QUESTION:
Is this second method better?
It has less clutter of small tables, just one table, making it easier to maintain the lists as they are all in the one table. It may make the database faster as there is less tables to connect to, but then there are more queries to be run on that table.

Note: I dont want to put my list of lookup values as a value list directly in the row source of the table and/or combo control as I want to be able to maintain the list of values easily (during design and also during future development and maintenance of the db).


Any comments and help is greatly appreciated.
Zollo9999
..........

Zollo A+ / VBA Developer

 

[PHV]

How do you maintain referential integrity with this method ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[zollo9999]

>How do you maintain referential integrity with this method ?

Simple:
Each value in each category is unique.

Generally this list of values is more likely to have things added to it than deleted.

I am in the early stages of design of a workplace going through lots of changes and it will be a while before terms are confirmed.

If I need to delete or change a value in the list, I will change all the values with a find and replace. This is something I'll do on occasions while developing the system and things change. Once the system design and function is more stable then this should not be a problem. If the system does keep needed development, then I or another coder will be able to handle the changes.

I think that covers it.

Any other opinions on the design?
Thanks
z


Zollo A+ / VBA Developer

 

[TheAceMan1]

How are ya zollo9999 . . .

There is no best way! . . . Remember [blue]you are the programmer[/blue] and sometimes you just have to take control! . . . Wether users agree or not! . . . This is your most [purple]avid role[/purple] as [blue]you know what they don't![/blue]

Myself . . . I do it my way ([blue]the way thats correct to me as the programmer[/blue]). I wait one . . . maybe three weeks to a month . . . then I go around getting user input! . . . Too many times I've come upon the great unexpected when it comes to users! . . . But it is the greatest thing to know, programming aside!

My point is . . . get thru the db the best way you know how . . . aka . . . get it done . . . and [blue]let the users prompt you for trimming![/blue]

[blue]Your Thoughts! . . .[/blue]

See Ya! . . . . . .

 

[zollo9999]

What the... that was a busy day, and last night my connection went down just before snooze time.

Hi AceMan

I agree with what you said,
although I have question or two still which I will answer for myself below.
Anyone else care to comment? ...

1) Are there any speed benefits using the one method over the other. My answer: I'm guessing "No" as the lookup tables are small.

2) Are there benefits in design time? My Answer: "Yes". For me anyway: Finding the right table to edit and sometimes opening the wrong table is a bit frustrating and time consuming to me.

If there is just one table with most of the fiddly lookup values in it, for me it will be easier to find to keep it up to date, and thus give more time for other design work.


So in my case, in this new project I will be trialling the single table and probably stick with it, but always looking for others opinions on what they think works best.

Also follow up on the question from PHV
The question of referential integrity (to my way of thinking) does not apply here as this table holds lookup values only, (not various different entities which would be a bit confusing technically). In fact you could argue that lookup values (all together) are a entity themselves and should be in one table.

Other real entities like "Staff", "Clients", "Orders" etc will of course have their own table each. Each of these real entities have other properties or values in columns and thus need there own table to remove redundancy. A table of lookup values doesn't a long list of fields/columns/properties.

Anyway thanks for all opinions.....
I'm still interested in other comments from anyone.

Zollo


Zollo A+ / VBA Developer

 

[TheAceMan1]

[blue] It gets a bit clumbsy with several small lookup tables [purple]each only containing 5 or so values[/purple].[/blue]

Have you considered a [blue]Value List[/blue] for these? . . . It would eliminate the tables!

See Ya! . . . . . .

 

[PHV]

Have you considered a Value List for these
Hey the Ace, hopefully you don't talked about this ?

http://www.mvps.org/access/lookupfields.htm

I ask as I don't have an US version of access and thus am not sure what 'Value List' means.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

Howdy [blue]PHV[/blue] . . .

Yes I'm aware of those evils. The typical problems when 1 field is bound while another is displayed.

Since [blue]zollo9999[/blue] is referencing list/comboboxes and has already shown concern for these lookup tables that have about 5 values, the [blue]Row Source Type[/blue] property of [blue]Value List[/blue] ([purple]semicolon delimited values for the rowsource[/purple]) would eliminate those tables.

Here were talking a single column for the list/combobox circumventing the evils! . . .

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[zollo9999]

Aceman
Regarding the Value list option, I'd rather put the values all in one table as it will be easier to maintain, track and add to them. I find going into the table design selecting the field selecting lookup and editing the values just too much to do.


PHV

Thanks for the link to

http://www.mvps.org/access/lookupfields.htm

I'd been there before (MVPS) but forgot about it. It's a good resource.

I will be avoiding all those evils of using lookups in my table and system design.

I'm using a lookup in the main table "now" while I'm doing the basic table design (without a form) and

I'm working out the entities and relationships, but when it comes to form design, I'll link a combo to

the lookup table and remove the lookups from the actual table.

As I said before, these lookups are just unique values with nothing else attached.

In this case, I'm NOT (repeat NOT) storing a unique Id of an Entity which has lots of other

data/values/fields/properties attached to it (with potentially lots of records also).

So give me a few weeks to get the system running and I expect it will work fine.
(I've got a lot of roles and system design is just one of them (one that I enjoy a lot).

Thanks
Zollo


Zollo A+ / VBA Developer