Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Unanswered: Need Opinion's on tbl design

    I need some opinion's on this table design. I'm working on an mdb design where someone designed this type of table:

    Table Name:
    sysCodes
    Fields:
    CodeID - autonumber
    CodeType - text
    CodeNumber - integer
    CodeText - text
    CodeSort - integer
    InActive - yes/no
    CodeNotes - memo

    The goal of this table is to make a "central" type table (ie. sysCodes) for all lookup values in the comboboxes. So for example, the following values would be populated

    CodeType = "CenterID"
    CodeNumber = 1
    CodeText = "Madison"
    CodeSort = 1
    InActive = False
    CodeNotes = "blank for now"

    and a second record would be...

    CodeType = "CenterID"
    CodeNumber = 2
    CodeText = "Milwaukee"
    CodeSort = 2
    InActive = False
    CodeNotes = "blank for now"

    Then in the specific combobox on the front-end form (and in almost all queries), the SQL Statement has criteria based on this table where
    CodeType = "CenterID" and sorted by CodeSort.

    I'm not quite sure if I like or dislike this. I like the fact that almost all combobox values can be populated in one table but I also wonder the impact of speed and performance of having every combobox on the form and a lot of queries being based on one table (that table relationally joined in all the queries always with criteria), over a non-SQL Server (Jet table), multiple-users (15-20) some accessed via a slow Citrix connection), and all users in 2 mdb's (split-frontend/backend mdb) system, and again, all based on this one SysCodes table in the backend. I'm suspecting I could have a few problems unless I separate the lookup type values into multiple tables.

    I'm interested on hearing if anyone has some pro/con opinions on this?
    Last edited by pkstormy; 09-08-08 at 20:45.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I've always thought of this kind of thing as a design fault... if it is what I think it is... a OTLT (one true lookup table).

    http://www.dbazine.com/ofinterest/oi-articles/celko22

    http://tonyandrews.blogspot.com/2004...-mistakes.html
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its pants in my books
    you've lost any data integrity by having a central fish tank.

    the performance issue could arguably be improved by transferring the table to a local Jet table on startup

    it has the smack of a developers perception of efficiency and code portability (ie to stick to one design, import or reuse code and ignore the users perceptions or data integrity issues)
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Some more links:
    Dave's guide to the EAV (http://weblogs.sqlteam.com/davidm/articles/12117.aspx)
    OTLT and EAV: the two big design mistakes all beginners make (http://tonyandrews.blogspot.com/2004...-mistakes.html)
    One True Lookup Table (http://www.dbazine.com/ofinterest/oi-articles/celko22) by Joe Celko
    Lookup Table Madness (http://www.sqlservercentral.com/colu...blemadness.asp)
    Some of these are EAV, but OLTP is just EAV's little brother IMHO.

    I've also seen someone else writing on the interwebz calling them "muck tables".

    EDIT - actually, two of them StarTrekker has linked to already.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Thanks for the links guys and the input. This is kind of what I thought but I appreciate the backup proof I need to redo this.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •