Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table design preferences (All)

    Just wondering what fellow loungers try to limit their tables to (as far as numbers of fields, etc..). I'm only storing text (for now), and plan on having memo fields on a separate table, but for my main table, I'm having trouble deciding what to limit it to. There are some items (i.e. personal info, height, weight, etc) that can be grouped together, but I don't want to have a table with just 5 fields, nor do I wish to have a table of 100 fields...what do you guys like to use?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Table design preferences (All)

    Personally, I don't regard the number of fields in a table as a consideration in designing a database. I don't mind having a table with just one field if it useful, two fields is quite common, and 100 would not fuss me if the situation required it.

    I am not sure what you mean by "items that be grouped together"? Do you mean put them all in the one field? I would not do that, as you then have to do the work of pulling them apart whenever you want any bit. It is much easier to put two fields back together in a query than to brak one field into two, so if in doubt, break things into smaller chunks rather than larger ones.
    Regards
    John



  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table design preferences (All)

    <hr>Do you mean put them all in the one field? <hr>
    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Nonono, thank you for the consideration, I've been lucky enough to not have made that mistake <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. What I meant was data that was similar in it's reference (i.e. a list of contact addresses in a separate table, or a list of schools attended, etc...)
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Table design preferences (All)

    I agree with John: number of fields is not a determining factor when designing a table. The basic idea is: avoid unnecessary repetition - repetition between fields and repetition between records.

    A lookup table may have only one or two fields; a "data" table may have a lot of fields.

    Any good treatise on relational database design will expand on this.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table design preferences (All)

    Is there a relation between query speed and number of fields/linked tables, (will a query with more linked tables run more/less efficiently if those fields were all in the same table, even if the table had 100 fields?)
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Table design preferences (All)

    It depends on the structure of the tables and queries. In general, a query based on one table is faster than a query based on several linked tables if you just retrieve records. But if you have to perform complicated calculations on one table because of a faulty design, the one-table query might be very inefficient.

    Example: you have a survey with 125 questions. Each answer is a number on a scale from 1 to 5.
    You could create a table with some data about the respondent, then 125 fields Response1 to Response125.
    Or you could create one table with respondent data and a respondent ID, plus another table with respondent ID, question ID and respomse.
    Calculating the average response for a respondent is MUCH easier/faster in the latter setup, because you can use the built-in Avg function.

Posting Permissions

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