Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

Thanks.

share|improve this question
    
What RDBMS are you using ? –  Kin Sep 4 '13 at 17:46
    
@Kin MySQL 5.1.70 –  enrmarc Sep 4 '13 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.

share|improve this answer
    
Thanks. I'm not sure about triggers, I need to execute PHP code. –  enrmarc Sep 4 '13 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.