One Master LU Table vs. Many LU Tables

On Jun 5, 3:59 pm, "Amy Blankenship"
wrote:
Personally, I think your design is just fine. 1400 rows is not a lot.

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?

Jamie.

--

On Jun 5, 6:50 pm, "Amy Blankenship"
wrote:
Have I shot my application in the foot...?

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...o-big-design-m...

I think it's interesting that your articles...

That's very flattering but I did not write those 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 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:

sSQL = _
"CREATE TABLE Lookups" & vbCr & "(code_type" & _
" CHAR(10) NOT NULL, " & vbCr & " CHECK(code_type" & _
" IN ('DDC', 'ICD', 'ISO3166'))," & vbCr & "" & _
" code_value VARCHAR(255) NOT NULL," & vbCr & "" & _
" CHECK" & vbCr & " (SWITCH (code_type" & _
" = 'DDC'" & vbCr & " AND" & _
" code_value" & vbCr & " " & _
" LIKE '[0-9][0-9][0-9].[0-9][0-9][0-9]'," & _
" 1, " & vbCr & " code_type" & _
" = 'ICD'" & vbCr & " AND" & _
" code_value" & vbCr & " " & _
" LIKE '[0-9][0-9][0-9].[0-9][0-9][0-9]'," & _
" 1, " & vbCr & " code_type" & _
" = 'ISO3166'" & vbCr & " " & _
" AND code_value" & vbCr & " " & _
" LIKE '[A-Z][A-Z]', 1," & _
" " & vbCr & " TRUE, 0) = 1)," & vbCr & "" & _
" code_description VARCHAR(255)" & _
" NOT NULL," & vbCr & " PRIMARY KEY (code_value," & _
" code_type));"
CurrentProject.Connection.Execute sSQL

sSQL = _
"INSERT INTO Lookups (code_type," & _
" code_value, code_description)" & _
" VALUES ('ICD', '500.000', 'Coal" & _
" workers'' pneumoconiosis');"
CurrentProject.Connection.Execute sSQL

sSQL = _
"INSERT INTO Lookups (code_type," & _
" code_value, code_description)" & _
" VALUES ('DDC', '500.000', 'Natural" & _
" Sciences and Mathematics');"
CurrentProject.Connection.Execute sSQL

sSQL = _
"INSERT INTO Lookups (code_type," & _
" code_value, code_description)" & _
" VALUES ('DDC', '507.800', 'Use" & _
" of Apparatus and Equipment in" & _
" Study and Teaching');"
CurrentProject.Connection.Execute sSQL

SSQL = _
"CREATE TABLE Books (" & vbCr & " isbn_10" & _
" CHAR(10) NOT NULL PRIMARY KEY," & _
" " & vbCr & " CONSTRAINT isbn_10__pattern" & vbCr & "" & _
" CHECK (isbn_10 LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]
[0-9][0-9X]')," & vbCr & "" & _
" CONSTRAINT isbn_10__checksum" & vbCr & "" & _
" CHECK (" & vbCr & "IIF(MID(isbn_10," & _
" 10, 1) = 'X', 10, CLNG(MID(isbn_10," & _
" 10, 1)))" & vbCr & "=" & vbCr & "((CLNG(MID(isbn_10," & _
" 1, 1)) * 1)" & vbCr & "+ (CLNG(MID(isbn_10," & _
" 2, 1)) * 2)" & vbCr & "+ (CLNG(MID(isbn_10," & _
" 3, 1)) * 3)" & vbCr & "+ (CLNG(MID(isbn_10," & _
" 4, 1)) * 4)" & vbCr & "+ (CLNG(MID(isbn_10," & _
" 5, 1)) * 5)" & vbCr & "+ (CLNG(MID(isbn_10," & _
" 6, 1)) * 6)" & vbCr & "+ (CLNG(MID(isbn_10," & _
" 7, 1)) * 7)" & vbCr & "+ (CLNG(MID(isbn_10," & _
" 8, 1)) * 8)" & vbCr & "+ (CLNG(MID(isbn_10," & _
" 9, 1)) * 9)" & vbCr & ") MOD 11" & vbCr & "), " & vbCr & "
ddc_code_value" & _
" CHAR(7) NOT NULL, " & vbCr & " ddc_code_type" & _
" CHAR(3) NOT NULL, " & vbCr & " CONSTRAINT" & _
" books_code_type__must_be_DDC"
sSQL = sSQL & _
" " & vbCr & " CHECK (ddc_code_type =" & _
" 'DDC'), " & vbCr & " FOREIGN KEY (ddc_code_value," & _
" ddc_code_type)" & vbCr & " REFERENCES" & _
" Lookups (code_value, code_type)" & vbCr & ")"
CurrentProject.Connection.Execute sSQL

sSQL = _
"INSERT INTO Books (isbn_10, ddc_code_value," & _
" ddc_code_type) VALUES ('0471579211'," & _
" '507.800', 'DDC');"
CurrentProject.Connection.Execute sSQL

Please explain how you propose replacing the compound (ddc_code_type,
ddc_code_value) with a single column (ddc) using the OTLT design. TIA.

Jamie.

--

On Jun 5, 6:55 pm, "David W. Fenton"
wrote:
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...?

No, I wouldn't say it's a problem.

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.

Yes, there are situations where a kludge is acceptable but I think the
best approach for the OP is to test the hypothesis, "Oops, I've made a
newbie error."

Jamie.

--

"Jamie Collins" wrote in message
oups.com...
On Jun 5, 3:59 pm, "Amy Blankenship"
wrote:
Personally, I think your design is just fine. 1400 rows is not a lot.

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.

On Jun 6, 9:56 am, Jamie Collins wrote:

OT: want to see the "Disappearing Access" trick?

Save your work then try this: I've created a syntax error situation by
removing a parenthesis from the otherwise valid SQL:

sSQL = _
"CREATE TABLE Lookups" & vbCr & "(code_type" & _
" CHAR(10) NOT NULL, " & vbCr & " CHECK(code_type" & _
" IN ('DDC', 'ICD', 'ISO3166')," & vbCr & "" & _
" code_value VARCHAR(255) NOT NULL," & vbCr & "" & _
" CHECK" & vbCr & " (SWITCH (code_type" & _
" = 'DDC'" & vbCr & " AND" & _
" code_value" & vbCr & " " & _
" LIKE '[0-9][0-9][0-9].[0-9][0-9][0-9]'," & _
" 1, " & vbCr & " code_type" & _
" = 'ICD'" & vbCr & " AND" & _
" code_value" & vbCr & " " & _
" LIKE '[0-9][0-9][0-9].[0-9][0-9][0-9]'," & _
" 1, " & vbCr & " code_type" & _
" = 'ISO3166'" & vbCr & " " & _
" AND code_value" & vbCr & " " & _
" LIKE '[A-Z][A-Z]', 1," & _
" " & vbCr & " TRUE, 0) = 1)," & vbCr & "" & _
" code_description VARCHAR(255)" & _
" NOT NULL," & vbCr & " PRIMARY KEY (code_value," & _
" code_type));"
CurrentProject.Connection.Execute sSQL

The problem is with Jet 4.0 OLEDB but it causes the *host* application
to disappear i.e. can be used to perform the "Disappearing Excel"
trick, etc.

Jamie.

--

"Jamie Collins" wrote in message
oups.com...
On Jun 5, 6:50 pm, "Amy Blankenship"
wrote:
Have I shot my application in the foot...?

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...o-big-design-m...

I think it's interesting that your articles...

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".

...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 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

On Jun 6, 4:31 pm, "Amy Blankenship"
wrote:
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.

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.

I took the time to put the SQL DDL in VBA code that you could run in
Access, from where you could also examine the objects created. Access
has very good interfaces for executing VBA and SQL DDL.

In other words, I made it as easy as I could for you to implement this
schema. Are you really saying that an *implementation* spec in
'ordinary English' would make it easier on you than VBA that you can
simply copy, paste and run? Mine is based on one of the articles which
you suggested you'd read, so if your need an 'ordinary English'
*requirements* spec then take another look at the article.

I don't find [Create table statements]
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.

I'm all for natural language definitions of business rules (e.g. see
http://www.inconcept.com/JCM/May1998/sharp.html). You're not a new
user so isn't this actually about comfort zones...?

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

Let me get this straight. I took a one table, three column example of
a third party you'd already seen and to which anyone can relate
(books), added a three column usage table, implemented it in Access/
Jet SQL and made it as easy as I could for you to implement. With a
grin you dismiss my implementation as 'technospeak' (what, exactly?
SQL? VBA? Constraints?), reject the example and instead proposed your
own 10 table, 22 column example, in your own field of expertise
(eLearning), and expect me to start from scratch? Is there any
interpretation other than discourtesy?

Our "normal" tables might be snipped

I wanted to be receptive of your schema but I found it hard work. It's
essentially a list of column names, only a few of which you describe
e.g. I truly have no concept of a PageID. To be honest, it took me
while to work out which are table names and which are column names,
and I still can't tell why 'Subject' and 'Chapter' have no columns nor
why 'Distractors' and 'PageMediaMap' are mentioned at all.

No data types, no constraints (other than the three FKs alluded to
rather than defined), no example data. To cap it all, you haven't even
given more than a hint as to how the tables 'Attributes' and
'AttributeSets' fit in to the rest of the schema.

The relationship is more "deduced" by naming the set the
same as the field name.

The best I can do is point out that you are mixing data and metadata
and urge you to investigate why this is itself a design flaw.

If you're a stickler for integrity, this might not work for you

I think you've hit the nail on the head.

Sincere thanks for taking the time, though.
Jamie.

--

On Jun 6, 4:31 pm, "Amy Blankenship"
wrote:
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 :-)

http://groups.google.com/group/micro...9192f4979ff75c

What is the point of posting to say you have no intention of being
helpful?

-Amy