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

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

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

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

On Jun 11, 12:47 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> Kevin McCarthy wrote:
>
> | How about if I need ALL the columns in one query?
> SELECT * FROM ...

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?

On Mon, 11 Jun 2007 16:23:37 -0700, Kevin McCarthy
<mccarthyk@gmail.com> wrote:

>On Jun 11, 12:47 pm, "Paul Lautman" <paul.laut...@btinternet.com>
>wrote:
>> Kevin McCarthy wrote:
>>
>> | How about if I need ALL the columns in one query?
>> SELECT * FROM ...
>
>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?

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.

Kevin McCarthy wrote:
> 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,

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.
jstucklex@attglobal.net
==================

== Quote from Jerry Stuckle (jstucklex@attglobal.net)'s article
> Kevin McCarthy wrote:
> > 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,
> 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.


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

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
>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.

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 McCarthy wrote:
> 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
>>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.
>
> 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
>

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.