2 million row database from hell! help a newbie in over his head.
Date: 21 Aug 2001 10:54:38 -0700
Message-ID: <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