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