3

I have a table, lets call table a, that I allow users to enter data into. They can enter data as many times as they want, and based on some constraints, I only use the most recent entry.

My question is this: is it okay to take the entered data from table a, simplify it (take the most recent data, apply some formulas), and store it in table b?

This improves access access for my application and convert makes most of my data access queries simple selection statements. The down side is that my data is replicated, and a script must be run regularly to keep data in sync.

So, is this an acceptable practice?

Improve this question
2
  • Everything is acceptable... as far as it works for your use-case (and doesn't put the world at risk of any deadly catastrophe) ;-) - Is this uncommon? Not really, you're actually emplying a poor man's version of a materialized view. – joanolo Feb 26 '17 at 19:40
  • @joanolo I had no idea that was what it was called. Thank you! – Alex Feb 26 '17 at 20:19
1

This sounds like the classic case of History versus CurrentState. Sure, have two tables. Insert a new row into History and overwrite the row keyed to the user in the other table.

That does lead to "redundant" data, which is a 'no-no' by DB purists. So here is another approach:

Insert into History, as above. Get back the AUTO_INCREMENT id. Then Update the CurrentState with just the id (and user_id). Then, for accessing the current info, use a JOIN between the tables.

Improve this answer
2
  • That's an interesting idea, thank you! What do you think of storing results of a function in a new table, vs calling them on demand? – Alex Feb 27 '17 at 3:06
  • "results of a function" -- is this related, or a separate function? How complex is the function? – Rick James Feb 27 '17 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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