0

Hello I am developing a levelsystem for a web application.

What i have:
Database:

table: levels -- fields: level_id, level_points

This table defines the levels from 0 to 20 with different point values. 

table: profiles -- relevant fields: profile_id, profile_points, profile_level

In profile_points will the points of a user be saved. In profile_level should be the level the user has now, like the levels table says.

My Question:

Is there anyway to make an mysql function which automates this. What I mean is, that if i insert into profile_points that mysql database knews that there was a level up, and inserts automatically the new level into profile_level?

No solution: No solution for me is, to calculate the level always new for the web application'!

Thanks for your help!

Esteban P.
  • 2,789
  • 2
  • 27
  • 43
Burtscher_Florian
  • 48
  • 12
  • 1
    Check out triggers. – jarlh Jul 11 '17 at 09:14
  • can you give an example? just a note, profile_points and profile_level were on the same table. – Ikyong Jul 11 '17 at 09:28
  • triggers is a possibility. But I'd be wary of putting business logic like this into your database, especially if it's the application layer which contains most of the rest of it. Your application code could much more easily do this, and then use a transaction to wrap up both SQL statements for the two inserts (to ensure consistency). Then again...a lot of database designers would say if there's a simple enough relationship between points and level, you shouldn't store it and should calculate it when you need it, as storing data which can be trivially derived from other data is redundant. – ADyson Jul 11 '17 at 09:40

1 Answers1

0

You can use triggers.

I do not advise this use because, for some error the trigger can be disabled and your system goes wrong.

I suggest that you do not store calculated fields in your database in this way, you are very susceptible to crashes.

Eduardo Lima
  • 54
  • 4