Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Boise, Idaho, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table Structure (2000)

    I have currently a table with 49 fields. There are at least 2 distinct groups of record information that could easily stand alone in a one-to-one relationship witht main table. One group has 14 fields the other is 12 fields - (I know about normalizing - each field is distinct to the record)
    I am wondering if I should have 3 tables instead of 1. Will it make a difference in efficiency or effectiveness if the main table has so many records? Is there a suggested limit on the number of fields a particular table should have?

    I've not dealt with a database that needed so much information related to one record so I've never thought of this as a potential problem until now.

    Any advice would be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Structure (2000)

    If you really would have a one-to-one relationship between the tables, I wouldn't split the table, unless you get error messaged that the record is too large. An Access table can have up to 255 fields, but the maximum size of one record (excluding memo and OLE fields) is 2,000 bytes (characters). If you're not in danger of reaching this limit, I would leave the table as it is. Multiple tables with one-to-onerelationships are much harder to manage than a single table, and perfomance would be negatively affected.

    If you have a lot of repetitive data in some fields, you might be able to split those off into a separate and have a one-to-many relationship. That would be more efficient. It depends on the contents of your records whether this is possible.

  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: Table Structure (2000)

    Just because you can divide the information into 2 groups is not a compelling reason in and of itself to justify creating 2 more tables linked one-to-one with the main table, at least IMHO. As Hans pointed out, your 49 fields aren't a problem for Access.

    There are probably 2 situations in which I'd consider splitting off the groups into their own tables. The first is when you are bumping up against Access's limit of 32 indexes and relationships on a table. By moving some fields to another table, you can add new indexes and relationships.

    The other situation is when the information in those groups applies only to certain records in the main table; and you would therefore only need to create records when needed. For example, if you had a record in main that only needed to use Group A info, you would create a record in Group A table, but wouldn't create a record in Group B. Processing that involved either of the Group tables would be quicker because you would be working with smaller recordsets.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Location
    Boise, Idaho, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Structure (2000)

    Thanks to you and Hans for your information. I was thinking that way but figured it wouldn't hurt to ask the "experts".
    I appreciate your time.

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

    Re: Table Structure (2000)

    I'm going to buck the trend here because you are talking about different groups of fields. If some of those fields depend on secondary keys (like some kind of record type field), then they should be split, especially if they do not get populated for every record. If they don't apply to every record, that is a valid reason for a one-to-one relationship. For instance, if you have a customers table and some your customers are businesses and some individuals, then the fields peculiar to each type belong in separate tables while the common fields remain in the customers table. Otherwise, you're in 2nd normal form at best and that is usually NOT the most efficient design for long term maintenance and flexibility.
    Charlotte

  6. #6
    Star Lounger
    Join Date
    Apr 2002
    Location
    Boise, Idaho, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Structure (2000)

    Charlotte
    thanks for the insight. I really appreciate everyone who took the time to answer my question.

    In this particular case I will keep the table as it is. Each field should be completed for each record and it's not really as large as I thought it was. Besides, it's a whole lot easier --- and there are times when 'easy' saves my sanity..

Posting Permissions

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