One Master LU Table vs. Many LU Tables

[KitCaz]

In a long-term fit of normalization, I designed my application to have one
master lookup table with a "type" column (where the lookup values were
commonly similar--code, desc, long desc, etc.).

I have 33 different types of lookup fields with (currently) ~1,400 rows.
There are 17 columns in this table in the end (various generic extension
fields which are used in different ways based on the type of lookup).

Have I shot my application in the foot with regards to performance in any
way by doing this? Or should I have created 33 different tables with many
fewer records?

My application runs fairly quickly on our office network once it is open,
but it's pretty sluggish on a cable connection. There is some load time too,
so I am looking into all possible ways to increase performance, and I
wondered if loading my lookup dropdowns on my forms was impacted by my design.

 

[Stefan Hoffmann]

hi Kit,

In a long-term fit of normalization, I designed my application to have one
master lookup table with a "type" column (where the lookup values were
commonly similar--code, desc, long desc, etc.).

Even this seems to be a good idea, it has one major flaw:

It breaks the entity relationships as you are mixing different
attributes or even weak entites.

I have 33 different types of lookup fields with (currently) ~1,400 rows.
There are 17 columns in this table in the end (various generic extension
fields which are used in different ways based on the type of lookup).

Can you give us short example data?

Have I shot my application in the foot with regards to performance in any
way by doing this? Or should I have created 33 different tables with many
fewer records?

Depending on the data distribution per page this may be a bottleneck.

My application runs fairly quickly on our office network once it is open,
but it's pretty sluggish on a cable connection. There is some load time too,
so I am looking into all possible ways to increase performance, and I
wondered if loading my lookup dropdowns on my forms was impacted by my design.

Have you all the default speed ups in your application? E.g. the hidden
recordset?


mfG
--> stefan <--

 

[KitCaz]

Hi Stefan,

If I understand you correctly about entity relationships, I should mention
that all the lookup fields are integers and the look up key is also an
integer, so the attributes are the same. In the Relationships diagram, the
multiple relationships to the same table ("LU") are automatically joined as
"LU", "LU_1", "LU_2", etc.

E.g., simple example in my table:

RecNo: autonumber
Status: integer field
Priority: integer field

Sample record:
RecNo=1, Status=1, Priority=3

In my lookup table "LU":

ID=1, Type=Status, Desc=Open
ID=2, Type=Status, Desc=Closed
ID=3, Type=Prio, Desc=Hi
ID=4, Type=Prio, Desc=Med
ID=5, Type=Prio, Desc=Low

Re: "default speedups" I'm embarrassed to say I don't know what you mean,
but in my old Paradox days that term applied to alternate indexes? My "LU"
table's [Type] column is indexed so that the combo box rowsource queries
which attempt to display only one type of lookup for each control should be
optimized (e.g. the [Status] dropdown only shows "LU" records for "Status",
etc.)

Chris

 

[Stefan Hoffmann]

hi Chris,

ID=1, Type=Status, Desc=Open
ID=2, Type=Status, Desc=Closed
ID=3, Type=Prio, Desc=Hi
ID=4, Type=Prio, Desc=Med
ID=5, Type=Prio, Desc=Low

The values displayed, e.g. "Open" or "Hi", are not of the same kind.
They are not the same "class".

Data is stored in pages on your disk. So are indices. If you have a
large table, then you have a large index distributed over many pages.
The number of pages Jet has to read is direct proportional to the speed
accssing your data.

So when you use lookup tables for each class/attribute then Jet needs to
scan a lower number of pages to find your values. For example your
priority index should be small enough to need only on index page and
also one data page.

Re: "default speedups" I'm embarrassed to say I don't know what you mean,
but in my old Paradox days that term applied to alternate indexes? My "LU"
table's [Type] column is indexed so that the combo box rowsource queries
which attempt to display only one type of lookup for each control should be
optimized (e.g. the [Status] dropdown only shows "LU" records for "Status",
etc.)

Indices are good. The major things about speeding up your application:

- Split your database into a front end and a back end. The back end only
stores the tables. Each user gets its local copy of the front end.

- Open a hidden form in the front end. In its form open event open a
recordset to a linked table.

- Use a normalized data model.


mfG
--> stefan <--

 

[KitCaz]

1) Re: classless data: Ok, so even though my lookup structure is similar for
all the different fields (ID, Desc, LongDesc, you're saying that the fields
to which they're linked aren't the same "class" and thus should be separated?

2) Re: index/page size, I get it. I will approach splitting my one LU table
into 33 tables. WHAT FUN!

3) Re: speedup: I am definitely working with a split database, back
end=tables, front end=forms/code. What's the difference between opening a
hidden form to open a recordset to a linked table vs. opening my first formal
application form with a recordsource that queries a linked table, or an
initial procedure that opens a recordset to get some start up rows from a
linked table?

hi Chris,

ID=1, Type=Status, Desc=Open
ID=2, Type=Status, Desc=Closed
ID=3, Type=Prio, Desc=Hi
ID=4, Type=Prio, Desc=Med
ID=5, Type=Prio, Desc=Low

The values displayed, e.g. "Open" or "Hi", are not of the same kind.
They are not the same "class".

Data is stored in pages on your disk. So are indices. If you have a
large table, then you have a large index distributed over many pages.
The number of pages Jet has to read is direct proportional to the speed
accssing your data.

So when you use lookup tables for each class/attribute then Jet needs to
scan a lower number of pages to find your values. For example your
priority index should be small enough to need only on index page and
also one data page.

Re: "default speedups" I'm embarrassed to say I don't know what you mean,
but in my old Paradox days that term applied to alternate indexes? My "LU"
table's [Type] column is indexed so that the combo box rowsource queries
which attempt to display only one type of lookup for each control should be
optimized (e.g. the [Status] dropdown only shows "LU" records for "Status",
etc.)

Indices are good. The major things about speeding up your application:

- Split your database into a front end and a back end. The back end only
stores the tables. Each user gets its local copy of the front end.

- Open a hidden form in the front end. In its form open event open a
recordset to a linked table.

- Use a normalized data model.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Chris,

3) Re: speedup: I am definitely working with a split database, back
end=tables, front end=forms/code. What's the difference between opening a
hidden form to open a recordset to a linked table vs. opening my first formal
application form with a recordsource that queries a linked table, or an
initial procedure that opens a recordset to get some start up rows from a
linked table?

Tony has explained it very well:

http://www.granite.ab.ca/access/performanceldblocking.htm

mfG
--> stefan <--

 

[Amy Blankenship]

In a long-term fit of normalization, I designed my application to have one
master lookup table with a "type" column (where the lookup values were
commonly similar--code, desc, long desc, etc.).

I have 33 different types of lookup fields with (currently) ~1,400 rows.
There are 17 columns in this table in the end (various generic extension
fields which are used in different ways based on the type of lookup).

Have I shot my application in the foot with regards to performance in any
way by doing this? Or should I have created 33 different tables with many
fewer records?

My application runs fairly quickly on our office network once it is open,
but it's pretty sluggish on a cable connection. There is some load time
too,
so I am looking into all possible ways to increase performance, and I
wondered if loading my lookup dropdowns on my forms was impacted by my
design.

Personally, I think your design is just fine. 1400 rows is not a lot.

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

HTH;

Amy

 

[Amy Blankenship]

One True Lookup Table
http://www.dbazine.com/ofinterest/oi-articles/celko22

Common Lookup Tables
http://www.projectdmx.com/dbdesign/lookup.aspx

OTLT and EAV: the two big design mistakes all beginners make
http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html

I think it's interesting that your articles assume it's necessary to have a
composite key to correctly reference the properties listed in the lookup
table, when actually all that is needed is a separate lookup table that
groups the lookups into "sets" that can be consumed by the tables they apply
to. This removes most of the disadvantages cited in the articles. I once
had to do this when it turned out that one set of lookups for a table was
not sufficient--that each parent of the records in that table could have a
different set of lookups that applied.

So even if you do create a separate table for the lookups on each table,
quite often that's not sufficient in and of itself. So why not at least be
aware of techniques that will allow you to handle odd situations when they
come up?

 

[David W. Fenton]

In a long-term fit of normalization, I designed my application to
have one master lookup table with a "type" column (where the
lookup values were commonly similar--code, desc, long desc, etc.).

I have 33 different types of lookup fields with (currently) ~1,400
rows. There are 17 columns in this table in the end (various
generic extension fields which are used in different ways based on
the type of lookup).

Have I shot my application in the foot with regards to performance
in any way by doing this? Or should I have created 33 different
tables with many fewer records?

My application runs fairly quickly on our office network once it
is open, but it's pretty sluggish on a cable connection. There is
some load time too, so I am looking into all possible ways to
increase performance, and I wondered if loading my lookup
dropdowns on my forms was impacted by my design.

No, I wouldn't say it's a problem. I've implemented the very same
thing in many of my apps, and here's a sample db I put together a
long time ago:

http://www.dfenton.com/DFA/download/Access/LookupAdmin.html

I use it only for lookups with a limited number of attributes
and for which the number of unique values is low, and for which
strict RI is not important.

 

[Amy Blankenship]

If you are basing your assessment on the number of rows, take another
look:

"There are 17 columns in this table in the end (various generic
extension
fields which are used in different ways based on the type of lookup)."

A 17 column *lookup* table? Fields used in different ways based on
type? Alarm bells?

OK, you're right about that part . But the idea that you'd have a lookup
table used _generally_ in the way described is not necessarily bad design.

 

[Amy Blankenship]

That's very flattering but I did not write those articles

I didn't mean it that way. It was shorthand for "the articles you posted".

I don't understand your proposed design. Could you perhaps explain
using an example?

For your convenience, here's the example OTLT in the Celko example
plus a Books table that 'consumes' (your term; I prefer 'REFERENCES')
its Dewey Decimal codes, implemented as Access/Jet SQL in VBA code:

I always tune out those Create table statements immediately. I don't find
them nearly as useful as describing the actual fields in the table and
saying what they do in ordinary English. They're certainly unlikely to be
useful to a new user. So for my convenience I've deleted them as so much
technospeak that obscures what's really going on . Access has a very
good interface for creating and maintaining tables.

So, let's look at how it could actually work in the world I am most familiar
with, eLearning.

Our "normal" tables might be (I'm expanding out the ones that might use
lookups):

Course
Subject
SubjectID
CourseID (FK to course)
SubjectDesc
SubjectOrder
SubjectType (might determine whether and how a subject is graded, for
instance)
Chapter
Page
PageID
ChapterID (FK to Chapter)
PageType (is this informational, does it contain a question, etc.)
PageOrder
etc.
Question
QuestionID
PageID (FK to page)
QuestionType (multiple choice, Drag/drop, etc.)
etc.
Distractors
Media
MediaID
FileName
MediaType (flash, image, sound, etc.)
etc.
PageMediaMap

Let's assume two other tables:

AttributeSets
AttributeSetID
AttributeSetDesc
DestFieldName

Attributes
AttributeID
AttributeSetID
AttributeDesc
AttributeOrder (optional)

Now, in the form, you simply have a row source:

SELECT AttributeID, AttributeDesc FROM Attributes
INNER JOIN AttributeSet On AttributeSets.AttributeSetID =
Attributes.AttributeSetID
WHERE AttributeSets.DestFieldName = SomeFieldName ORDER BY AttributeOrder

You can actually maintain the different lookup sets "as if" they were
different tables, because if you use a form/subform arrangement with the
AttributeSet, they will all appear in different recordsets in the subform.
This may well be neater and cleaner than having to change out the subform's
source or using a Union query.

If you're a stickler for integrity, this might not work for you, because
there is no hard and fast relationship between the AttributeSets and the
fields they feed. The relationship is more "deduced" by naming the set the
same as the field name. Additionally, this simple example probably works
best where you're looking up values where the "meaningful" part is a string.
However, I find it difficult to imagine you might look up a number or
Boolean value. It seems to me that in those cases it makes more sense to
just put the number or Boolean value directly in the field.

Hope this clarifies;

Amy