Table Design Best Practice Question 3

[mkrets]

I'm creating a new table that is going to have quite a few fields that will be lookups with set values. Is it best in that case to use other tables for the lookup values or to use set values within the Table Design?

 

[Remou]

Have a look at the links here thread700-1404970

You will see why look-up fields are not a good idea. You will also find a very useful link posted by lespaul

 

[johnherman]

Unless the domain of the values is absolutely fixed (like Y/N/U or Male/Female/Unknown), it is more flexible to use lookup tables.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[dhookom]

IMO, lookup fields defined in tables is never a good idea. I don't consider any list of lookup values fixed. I would always create lookup tables and use combo boxes on forms.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mkrets]

Thanks all. Those are the answers I was looking for.

 

[BobRodes]

<I don't consider any list of lookup values fixed

You must not work for the government....

 

[dhookom]

My comment implied that all lists should be assumed to be dynamic. I would think that government "lists" would change depending on the wind direction.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BobRodes]

<I would think that government "lists" would change depending on the wind direction

Not in my experience. The only things in government that change are the ones that attract the attention of politicians who are trying to get elected. Things like refactoring database schemas just aren't at all high on that list. They usually just throw everything in one flat file anyway, and raise taxes to cover the increased storage requirements.

At least that's so in the USA; perhaps the British are more advanced.