Say I have a table prices. Users can see the prices of this table (I'm simplifying it). Users also can send prices to the server, so the server is going to collect these data in another table (temp_prices).

When the server detects that there are 50 prices or more, then the server calculates a median of those temporal prices and update the table prices with the calculated value (again, very simplified).

The first idea that came to my mind was have two tables with the same schema but different names (prices and temp_prices).

I don't know if this is a good/common practice. I am missing something? Also, what mechanism should I use to trigger the server to inspect the table temp_prices? I think about cron but again, I don't know if there is something I'm missing (another tool or common practice for doing this kind of stuff).


  • What RDBMS are you using ?
    – Kin Shah
    Sep 4, 2013 at 17:46
  • @Kin MySQL 5.1.70
    – user6814
    Sep 4, 2013 at 17:54

2 Answers 2


Depending on the database you're using, you could have an ON INSERT trigger that checks if there are >= 50 records in prices and then runs the recalculate-and-update process (you don't have to do all of that in the trigger itself, you could separate it out into a stored procedure that is called by the trigger).

As for prices and temp_prices, I don't see anything terribly wrong with it. I imagine that the structures are the same, but they are being used in very different ways. If you did want to merge the tables, you would probably need to have an indicator of some sort to show which records came in from the users, and which ones were there before. Considering that there will be different kinds of activity on these two types of prices, it might make sense for performance reasons to leave them in separate tables.

  • Thanks. I'm not sure about triggers, I need to execute PHP code.
    – user6814
    Sep 4, 2013 at 22:06

You could do this using one table only, prices, by adding an extra column called IsTemporary (true or false).

The extra column determines whether the row is a temporary row or permanent. You could then add a view called prices_view that groups temporary rows together and calculates the median and count of rows grouped together.

Use this view to update the permanent rows when the count is over 50. The update could be applied with a cron job or use a view that returns a permanent row or the median of temporary rows when there are 50 or more.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.