Professional Web Applications Themes

n00b DB design question: Gigantic tables - MySQL

Hey all, I've got a question about how to design my database. I have all this census data (around 500 fields) for every city in the us, and I'd like to be able to run queries that retrieve a subset of these fields (maybe like 100 of them) for a given city. How should I do this? Is it best to have a massive table with 500 rows and thousands of columns or somehow divide it into several tables and join them all together when I do a query? How about if I need ALL the columns in one query? ...

  1. #1

    Default n00b DB design question: Gigantic tables

    Hey all, I've got a question about how to design my database. I have
    all this census data (around 500 fields) for every city in the us, and
    I'd like to be able to run queries that retrieve a subset of these
    fields (maybe like 100 of them) for a given city. How should I do
    this? Is it best to have a massive table with 500 rows and thousands
    of columns or somehow divide it into several tables and join them all
    together when I do a query?

    How about if I need ALL the columns in one query?

    Thanks,

    Kevin

    Kevin Guest

  2. #2

    Default Re: n00b DB design question: Gigantic tables

    > Is it best to have a massive table with 500 rows and thousands 

    Oops, mean to say "500 columns and thousands of rows"



    Kevin Guest

  3. #3

    Default Re: n00b DB design question: Gigantic tables

    Kevin McCarthy wrote:
    | How about if I need ALL the columns in one query?
    SELECT * FROM ...


    Paul Guest

  4. #4

    Default Re: n00b DB design question: Gigantic tables

    On Jun 11, 12:47 pm, "Paul Lautman" <com>
    wrote: 

    Right, so if I have everything in a gigantic table I could do that.
    Is that the best way to organize things, though? Or should I try to
    split the table up?

    Kevin Guest

  5. #5

    Default Re: n00b DB design question: Gigantic tables

    On Mon, 11 Jun 2007 16:23:37 -0700, Kevin McCarthy
    <com> wrote:
     
    >
    >Right, so if I have everything in a gigantic table I could do that.
    >Is that the best way to organize things, though? Or should I try to
    >split the table up?[/ref]

    Except in case you have a "few" columns that will be queried together
    a lot of times, I think it's no use splitting your tables. Well
    actually you can speed up the things in having two tables : one for
    constant-length parameters (numbers, char(n), enum,...), and one for
    all the variable length content (varchars, texts, blobs,...), because
    the queries on the constant-length data will be a lot more efficient,
    but it depends on the quantity of each of them. If you have only say
    1/10th of your columns constant-lengthed, it might result in 1/100th
    of the whole or less, and it's then no use to put them apart. Except
    if you query them 100 times more than the variable-lengthed columns.

    It's difficult to give you an answer. Give us some information about
    the types of columns you have, and the way your requests will be done
    (you provide only certain predefined queries or it's free request
    access ?), etc.
    subtenante Guest

  6. #6

    Default Re: n00b DB design question: Gigantic tables

    Kevin McCarthy wrote: 

    Kevin,

    You could start with some theory on database normalization techniques
    (don't worry - it's neither hard nor is it deep). Google for "database
    normalization", for a start.

    Anytime I see 500 columns in a single row I wonder about the design.
    Sure, there are valid reasons for having that many columns, but more
    often than not it's an indication of a poor design.

    But you also haven't given us enough information to really be of assistance.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  7. #7

    Default Re: n00b DB design question: Gigantic tables

    == Quote from Jerry Stuckle (net)'s article 
    > Kevin,
    > You could start with some theory on database normalization techniques
    > (don't worry - it's neither hard nor is it deep). Google for "database
    > normalization", for a start.
    > Anytime I see 500 columns in a single row I wonder about the design.
    > Sure, there are valid reasons for having that many columns, but more
    > often than not it's an indication of a poor design.
    > But you also haven't given us enough information to really be of assistance.[/ref]


    from the perspective of mysql performance, the performance goes down as the number
    of columns increases! best practice is to divide the data and load them in several
    different but somewhat meaningful tables. for example you'd like to have all the
    data related to demographics in one table, another table can contain data related
    to industry and/or educational institutions and so on and so forth.

    make sure you'll find a field that is common amongst all of the data so you can
    join the tables if need be. further, you'd like to place proper index fields on
    each table based on your queries.

    hope this helps.
    --
    POST BY: lark with PHP News Reader
    lark Guest

  8. #8

    Default Re: n00b DB design question: Gigantic tables

    Cool, thanks for all the great information! I'm reading up on
    normalization - there's so much to learn about databases.

    == Quote from lark's article 

    Just to be clear, you are saying that this would only improve
    performance in the case that my queries do not ask for all the fields
    at once, right? Or would dividing my tables in this manner improve
    performance even though I JOIN all of them every time I query them?



    A little more information about my database:

    1. Luckily, ALL of the data is either a float or an integer.
    2. Each row represents a city. Cities are in a different table, but I
    join them together with their census data based on an integer key.
    3. Requests will come from a script that will mostly do the same query
    every time. Most of the time, I will want all of the fields, so if
    it's all in one big table, it will probably be a SELECT * FROM ...
    WHERE city_id=

    If I understand you guys correctly, there are only two reasons why I
    would want to split my table:

    1. I don't need every field every time - split the table into subsets
    based on usage, like if I only needed the first 10 fields most of the
    time, and all of the fields only some of the time.
    2. The fields have different data types - keep all the of the
    different data types together in different tables

    I might actually end up using this same database for a couple of
    different applications, one of which would not require all the data
    every time, in which case I might end up splitting the tables for
    reason #1.

    Thanks again for your great responses!

    Kevin

    Kevin Guest

  9. #9

    Default Re: n00b DB design question: Gigantic tables

    Kevin McCarthy wrote: 
    >
    > Just to be clear, you are saying that this would only improve
    > performance in the case that my queries do not ask for all the fields
    > at once, right? Or would dividing my tables in this manner improve
    > performance even though I JOIN all of them every time I query them?
    >
    >
    >
    > A little more information about my database:
    >
    > 1. Luckily, ALL of the data is either a float or an integer.
    > 2. Each row represents a city. Cities are in a different table, but I
    > join them together with their census data based on an integer key.
    > 3. Requests will come from a script that will mostly do the same query
    > every time. Most of the time, I will want all of the fields, so if
    > it's all in one big table, it will probably be a SELECT * FROM ...
    > WHERE city_id=
    >
    > If I understand you guys correctly, there are only two reasons why I
    > would want to split my table:
    >
    > 1. I don't need every field every time - split the table into subsets
    > based on usage, like if I only needed the first 10 fields most of the
    > time, and all of the fields only some of the time.
    > 2. The fields have different data types - keep all the of the
    > different data types together in different tables
    >
    > I might actually end up using this same database for a couple of
    > different applications, one of which would not require all the data
    > every time, in which case I might end up splitting the tables for
    > reason #1.
    >
    > Thanks again for your great responses!
    >
    > Kevin
    >[/ref]

    from what you have described above, i think you now have a better
    understanding of the issues.

    to answer your question above about the performance, the performance of
    your tables increases regardless of whether queries ask for all fields
    at once or not! likewise, if tables are somewhat logically divided and
    properly indexed, the performance of your joins will increase regardless
    of how many columns/rows you return.


    hope this helps.
    lark Guest

Similar Threads

  1. total n00b question
    By mkauspe in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: April 1st, 08:58 AM
  2. n00b importing question
    By gsurlak webforumsuser@macromedia.com in forum Macromedia Director 3D
    Replies: 2
    Last Post: October 8th, 05:53 PM
  3. Question About Navigation Bar (N00B Inside)
    By Existentialism webforumsuser@macromedia.com in forum Macromedia Flash Sitedesign
    Replies: 1
    Last Post: September 9th, 09:53 AM
  4. Plz help a n00b with a simple question..
    By Krusty^ in forum Macromedia Dreamweaver
    Replies: 2
    Last Post: August 5th, 06:45 PM
  5. sending out an SOS - not a n00b Question but very complicated
    By SHiKai webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 3
    Last Post: July 15th, 12:38 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139