 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / June 2007One Master LU Table vs. Many LU Tables
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. 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 <-- 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 > hi Kit, > [quoted text clipped - 25 lines] > mfG > --> stefan <-- 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 <-- 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, > [quoted text clipped - 34 lines] > mfG > --> stefan <-- 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 <-- > 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 [quoted text clipped - 14 lines] > 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 On Jun 5, 3:59 pm, "Amy Blankenship" <Amy_nos...@magnoliamultimedia.com> 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, 3:59 pm, "Amy Blankenship" > <Amy_nos...@magnoliamultimedia.com> wrote: [quoted text clipped - 9 lines] > 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. > 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 [quoted text clipped - 5 lines] > > 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/10/otlt-and-eav-two-big-design-mistakes.html etc etc Jamie. -- >> In a long-term fit of normalization, I designed my application to have >> one [quoted text clipped - 15 lines] > 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? On Jun 5, 6:50 pm, "Amy Blankenship" <Amy_nos...@magnoliamultimedia.com> wrote: > > > Have I shot my application in the foot...? > > [quoted text clipped - 8 lines] > > 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. -- 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. -- > On Jun 5, 6:50 pm, "Amy Blankenship" > <Amy_nos...@magnoliamultimedia.com> wrote: [quoted text clipped - 12 lines] > > 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 [quoted text clipped - 9 lines] > 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" <Amy_nos...@magnoliamultimedia.com> 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 [quoted text clipped - 3 lines] > 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" <Amy_nos...@magnoliamultimedia.com> 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/microsoft.public.access/msg/fa9192f4979ff75c What is the point of posting to say you have no intention of being helpful? -Amy > In a long-term fit of normalization, I designed my application to > have one master lookup table with a "type" column (where the [quoted text clipped - 14 lines] > 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.  SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ On Jun 5, 6:55 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid> wrote: > > In a long-term fit of normalization, I designed my application to > > have one master lookup table with a "type" column (where the [quoted text clipped - 12 lines] > 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. -- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.