How can I retrieve the object properties values from the database?

When designing a relational database schema, when should fields be mutable, versus when should they be in a separate table with immutable values?

• For example, I'm modelling a Quora user and their credits. I already have a separate table for transactions - earning credits, and spending credits. I just need to store the Quora user and their credits. The simple solution is to make the table mutable. The table has a field for the user ID and a field for the credits. The field for the user never changes, but the field for the credits changes through time. However, this looks  like an object-oriented programming  model with mutable state. In a functional-style program, the object would be immutable. To increase the credits, instead of modifying the user object, a new user object is created with the same user ID but with a change in the credits. Does it make sense to do this in the database as well? This will now be two tables, with the second table containing the different values of the credits as they vary in time.

• Answer:

  In database terms everything is mutable. You have data sets, and you can change the values, and your pointer to the data is the primary key of the table. You never change the pointer when the data changes, but instead change the data. That's because the relations between tables are using those pointers (keys) and changing them would mean expensive updates of all indexes and relations associated. If your problem is tracking the data changes over time, this can be done by using either an orthogonal normalized table or by adding metadata to the object.