Good of One vs. Good of Many

KJZZ Webmaster said:
> I am working on an application using MySQL where about 100 records will
> be entered into a table every day.
>
> Is it a good idea to create separate tables with the date as the title
> of each table? Or would it be better to have a date field and add all
> records to a single table?
>
> I would think that creating many tables would add to the speed of the
> application.
> However wouldn't this lead to difficulty in searching the tables, or is
> there a sql statment for searching across many tables?
>
> I may post this on the MySQL list as well, but I thought I would start
> here first.
>
> Thanks in advance for your help.

I would need more requirement definitions to give a proper assessment.
Upfront, I would say you are better off doing this as a single table with
a column for date. 100 records per day would be 36,500 a year, which is
rather small for a database table. One thing you could do if you were
worried is create and archive table. That is every 30 or 60 days archive
by date transactions into a archive table. This archive table would then
have every transaction for reporting purpose, but your main table would
only have 30 to 60 days worth of data, thus being much more snappy.
However, without having the requirements I don't know if that fits your
business needs.

My Two Cents. If you would like to talk in more detail you can contact me
at 480-330-7892 and give me more details and I can provide a more informed
answer.

-Derek