--- KJZZ Webmaster <
kjzz.webmaster@riomail.maricopa.edu> wrote:
> 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?
Use one table with a date field, if the date is part of your data.
Storing data in table names is a maintenance nightmare and is in
principle a no-no.
If you use a date field, you can use the native SQL date functions to
query for date ranges... for example, if you wanted all the entries in
the past week, you could use
WHERE name_of_datefield > DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
I think that's right... my MySQL is a bit rusty. :)
If you store the date in table names, you have to iterate over the
tables in some sort of wrapper which performs the calculation; it
becomes really cumbersome for ad hoc queries. Plus you have to ensure
that when you perform the iteration, your wrapper's date output is in
EXACTLY the right format to match the table names. Not a big deal in
practice for a single developer, but over time and with others
performing queries, this could be a challenge. I know I'd rather take
advantage of MySQL's built-in date parsing capabilities.
> 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 would think just the opposite, especially for large date ranges. Each
query requires some overhead for setup and teardown. One query against
a properly indexed table should be much faster than, say, 365 separate
queries across 365 tables, even if the one query is expected to return
365 times as many rows.
If you want something that looks like a table for each day, you can
create views. When I have to look at schema design, I now take as a
general rule that anything resembling a "table with data in the table
name" should really be created as a view.
And, I agree with what Derek & Joe said in their responses as well.
-ATL
> I may post this on the MySQL list as well, but I thought I would
> start here
> first.
>
> Thanks in advance for your help.
>
> John T.
__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
(text/plain)