Re: 2 million row database from hell! help a newbie in over his head.

before you begin designing tables, find out what the top 20 questions are that need to be answered based on the data. having this information will provide a better understanding of how to design the database.

"dave rau" <dave_at_projectfx.com> wrote in message news:11c09d6a.0108210954.347c9b16_at_posting.google.com...
> I must preempt my message with the fact that I am a total newbie to
> database design both conceptually and in practice. I have been
> learning some mySQL and PHP recently and have recently taken on a very
> large database project at my cushy guuv-ment job to publish data to
> the web.
>
> I have encountered some problems, mostly dealing with how to
> restructure this database for optimization in mysql and for
> publishing/searching on the web.
>
> The database I am working with contains a rainfall site number, a
> collection date, a collection value and the status of the data,
> whether the data was incomplete, or if the power went out on a
> station, etc.
>
> I have paired importent columns down to the following: ID (*primary
> key), Site_number, Collection_date, value, and status. I am not even
> sure if this is the correct way to pair the data down however.
>
> The biggest problem is that the database currently lists values for
> every day, for 50 years, for 450 rainfall stations!!! Currently the
> database has over 2 million rows!! Newbie or not, this seems pretty
> excessive and clunky for publishing to the web.
>
> With that said, would I be better off by eliminating the Site_number
> column and making a table for EACH rainfall station (all 450 of them)?
> Should I make a table for each year? Keep in mind that all 450+
> stations have a value for every day for 50+ years, some go back to
> 1910 (90 years). Since zero is an acceptable value for rainfall it is
> possible that much of the value columns are zero.
>
> *also, I have been looking into some books on conceptual database
> design, is database design for mere mortals a recommended book on
> anyones list? I would be interested in some others. thanks in adv
> guys!
>
> dave
Received on Wed Aug 22 2001 - 05:22:57 CDT