Hi Scott,
regardless of how many records you have (60,000 is small for DB though),
i think it's better if the DB design and logic jobs are separated.
Assuming you have a correct index, saving them all in one large table is ok.
So, you can keep the current logic the same.
If you really care about DB performance, try using DB that supports
partitioning (i don't think mySQL 4.1 supports it though).
This way, the DB will take care of splitting your data virtually, and
you can keep the same SQL and logic.
Splitting same data into separate table is not a good idea for later use.
For example: if you want to generate report, or do some search and so on.
There will be more troubles to join those splitted tables if it was
done programmatically.
But if the DB do that for you (partition), it will be LOT easier.
The only reason i can think to split the information into several
tables is only when we create details and summary stuffs.
Other than that, let the DB does the job.
Just for comparison: we're now supporting a giant application and DB.
The database size now reach several TB (yes that's terra byte -
running on Oracle 10g).
Partitioning makes our life easier.
HTH,
Rizal
www.masrizal.com
www.image-compressor.com
At 12:39 PM 5/4/2008, you wrote:
>I'm building an application where the amount of data in a table will
>be growing fairly quickly. The particular application is a little
>complicated to explain, so for simplicity, let's say I was building
>a blog app in the following enviroment.
>
>Linux server w/apache
>MySQL 4.1
>Coldfusion 7.02
>
>I have 20,000+ users now (increasing at a rate of 300+ per month),
>and let's assume all will be making blog posts (2-3 per week).
>That's 60,000 entries per week.
>
>Should I have 1 table where I store the blog posts, writing the ID
>for the specific user in a column so I can retrieve only his/her
>posts when they are viewing their blog?
>
>OR
>
>Should I setup my code to create a new table for each user's blog
>posts, and then pass the table name dynamically when retrieving his/her posts?
>
>OR
>
>Split the data some other way?
>
>Thanks in advance for any recommendations.
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304715
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
