I am just grouping the flow data from each day into a seperate table until it is a week old and then I will aggregate the data some more. ... Does this still sound like a bad design?

Yes. By keeping data in separate tables, you're making more work for yourself. Databases are good at aggregating information, and it's easiest if all of the data is in a single table.

If I was doing this, I'd keep add a (non-key) field to hold the date, and would keep all data in a single file. Then, I could do something like 

  SELECT sum(somefield)
  FROM table
  WHERE datefield BETWEEN ? AND ?

[download]
to do aggregate calculations. Or 
  SELECT datefield, sum(somefield)
  FROM table
  WHERE datafield BETWEEN ? AND ?
  GROUP BY datefield

[download]
if I needed the aggregates to apply to distinct dates.

Flushing old data is easy. 

  DELETE from TABLE WHERE datafield < ?

[download]
These are all static queries. By keeping all data in a single table, I don't have to reconstruct new queries to plug in a dynamic table name. And I'm letting the database do the aggregate calculations, rather than pulling all of the data over the wire to the client and doing it there.

If your particular calculations are more complicated, and can't be done in SQL, you may still be able to do part of the aggregation on the server side, reducing the amount of data you have to pull across the wire into the client.

In reply to Re: Re: Re: Ignore Relation already exists error from DBI by dws
in thread Ignore Relation already exists error from DBI by global

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.