Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Sacramento, CA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    To break or not to break (large tables) (Accesss 2000)

    I've got a table with about 55 fields, and I'm trying to figure out whether to break it up into four or five smaller tables. I've already pulled out many more fields with which that table has one-to-many relationships. At this point, if I break up the table any further into smaller tables, the smaller tables will have a 1 to 1 relationship with each other. I'm trying to decide whether there's any advantage to having four smaller tables, since there's a 1 to 1 relationship.

    I realize that as a general rule, you try to avoid having large tables, and wherever you can eliminate redundancy by relating repetitive data to another table, that's the way to go. But in this case there is no repetitive data. Each field in a given record has data that's unique to that record, since I've already pulled out such repetitive things as City, State, and a few other redundant data fields. So my question is, there any other design or efficiency advantages to be obtained by breaking the larger table into smaller tables?

    Thanks in advance for any suggestions on this.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: To break or not to break (large tables) (Accesss 2000)

    I'm not sure what you mean about the smaller tables having a 1 to 1 relationship with each other. You wouldn't relate the smaller tables to one another, you would relate them to the main table that holds the irreduceable atomic record. I seriously doubt that you can have 55 fields in a table that can't logically be broken out further into categories of information, but without a hint as to what is actually in the tables, it's hard to do more than speculate.
    Charlotte

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: To break or not to break (large tables) (Accesss 2000)

    I don't think 55 fields in a table is too large. Granted, it is alot of fields; but if the table is normalized, then I see no good reason to artificially split it up. I would think creating several smaller tables would be LESS efficient than the single table, since you now have the additional overhead of having to join 2 or more tables for even the simplest queries.

    The exception might be if you have a block of fields that contain data only in specific situations. For example, suppose your table keeps track of people. You might have several fields that contain data only if person is a minor (such as parent/guardian name and phone, etc.). In this case, your new MinorInfo table would only contain records for minors.

    Another possible exception would be if you had blocks of data that you typically did not use much. Such would be the case with some static, historical information that you hardly ever use, such that you would typically not include these fields in most queries.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: To break or not to break (large tables) (Accesss 2000)

    I agree the some kinds of table can legitimately contain that many fields, but I've seen very few of those. Usually the tables I've seen with that many fields are simply badly designed.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Mar 2002
    Location
    Sacramento, CA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: To break or not to break (large tables) (Accesss 2000)

    Based on your comments, you've got me questioning whether I have really
    normalized my large table as much as I think I have.

    The records in my table are loan applications. About 20 of the fields
    represent information about the loan application itself: applicant name,
    address, amount requested, etc. I'm sure that can be one table. (I do have
    redundant data like state, date, etc. in other tables).

    The other 30-some fields are of two categories:

    1. About 15 of the fields are numbers representing criteria ratings on a
    scale of 0 to 100. Is it advantageous to put these numeric criteria rating
    fields into another table? I know I could easily display it in a subform
    with the main table, but I'm not sure I see the advantage to doing it that
    way, rather than just having all the data in the same table, and keeping it
    on different tab pages of the main form.

    2. The other 15 fields are simple check box fields representing steps in
    the application process that are either completed or not completed. In some
    cases, it's either a date completed or blank instead of a check box. Is
    there any advantage to putting that kind of data in another table and
    subform?

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: To break or not to break (large tables) (Accesss 2000)

    >>I agree the some kinds of table can legitimately contain that many fields, but I've seen very few of those. Usually the tables I've seen with that many fields are simply badly designed.<<

    I agree, which is why I prefaced my remarks with "If the table is normalized...". However, if it is normalized (which as you suggest is questionable with 55 fields), I still believe fragmenting the table into several smaller tables is generally not a good idea.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: To break or not to break (large tables) (Accesss 2000)

    I would think about things in this fasion:
    <UL><LI>You have people
    <LI>You have loan applications which belong to people
    <LI>You have ratings which probably won't exist when the loan app is initially made
    <LI>You have a series of steps which you have to go through to complete the loan - the steps may be different depending on the kind of loan, what is being financed, etc.[/list]We often make this last category a series of events with tickle dates, who is responsible for it happening, and some logic which determines what event must be completed next when a given kind of event is completed. It works pretty well for processes that are relatively complex (loan apps are - especially mortgages!!!!). There are some downsides if management wants detailed blow by blow reports that read across the page from right to left - if they insiste you may have to create temporary flat files to drive reports, but otherwise it seems to work very well.<UL>
    Hope this helps.
    Wendell

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: To break or not to break (large tables) (Accesss 2000)

    You get no argument from me there, Mark. I do use 1 to 1 relationships, but only for data that exists for only a limited number of records in the parent table or that only applies to a subset of the main table records.
    Charlotte

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: To break or not to break (large tables) (Accesss 2000)

    Nice summary, Wendell! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Charlotte

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: To break or not to break (large tables) (Accesss 2000)

    >>I do use 1 to 1 relationships, but only for data that exists for only a limited number of records in the parent table or that only applies to a subset of the main table records.<<

    And that makes perfect sense! From the original post, I was just afraid the intent was to get a lower number of fields per table by arbitrarily splitting the original table into 2 or more tables. I think that would likely be a mistake.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: To break or not to break (large tables) (Accesss 2000)

    Agreed in general, but one point occurred to me in perusing this post. Often some of the data in a main table is rarely or never referenced after an initial period. In that case it may make sense to split it off into a separate table, and use a query at such times as the data is actually needed. The concept doesn't follow the strict rules of normalization, but it may make sense in terms of performance.
    Wendell

  12. #12
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: To break or not to break (large tables) (Accesss 2000)

    >>Often some of the data in a main table is rarely or never referenced after an initial period. In that case it may make sense to split it off into a separate table, <<

    Yes, this is a reasonable "exception" for splitting-off normalized data to another table.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  13. #13
    New Lounger
    Join Date
    Mar 2002
    Location
    Sacramento, CA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: To break or not to break (large tables) (Accesss 2000)

    Many thanks to Mark, Charlotte and Wendell for the suggestions and insights you have given to me and the group.

Posting Permissions

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