I have a python script where the user can click a button to create columns of type numeric.

Every time the user click that button, two columns get created, for example: amt_day1, bal_up_to_day1.

Let’s say the user created these columns: amt_day1, bal_up_to_day1, amt_day2, bal_up_to_day2, amt_day3, bal_up_to_day3.

Here is an example of the result expected after inserting some data:

name, amt_day1,bal_up_to_day1,amt_day2,bal_up_to_day2,amt_day3,bal_up_to_day3
John,     100,       100,   150,       250,        50,    300
                        

I want the columns to be:

bal_up_to_day1 = amt_day1;
bal_up_to_day2 = bal_up_to_day1 + amt_day2;
bal_up_to_day3 = bal_up_to_day2 + amt_day3;
                                    

So if the user decides to update amount_day1, I want all the columns balance_up_to_day… to be updated automatically since there are accrued balance.

If the user wants to change the amount of the column amount_day1 from 100 to 400 for John, I should have the result :

name,amt_day1,bal_up_to_day1,amt_day2,bal_up_to_day2,amt_day3,bal_up_to_day3
John    400,      400,         150,      550,   50,   600
                        

I read about TRIGGER and dynamic SQL, but I still cannot find a way to do it.

Any solution is welcome.

I try: UPDATE accrued_earning SET bal_up_to_day1=amt_day1; The first time the user click.

For the other times, I try UPDATE accrued_earning SET bal_up_to_day2=bal_up_to_day1 + amt_day1;

But that should update the bal_up_to_day.. column dynamically.

I have a python script where the user can click a button to create columns of type numeric.

Every time the user click that button, two columns get created, for example: amt_day1, bal_up_to_day1.

Let’s say the user created these columns: amt_day1, bal_up_to_day1, amt_day2, bal_up_to_day2, amt_day3, bal_up_to_day3.

Here is an example of the result expected after inserting some data:

name, amt_day1,bal_up_to_day1,amt_day2,bal_up_to_day2,amt_day3,bal_up_to_day3
John,     100,       100,   150,       250,        50,    300
                        

I want the columns to be:

bal_up_to_day1 = amt_day1;
bal_up_to_day2 = bal_up_to_day1 + amt_day2;
bal_up_to_day3 = bal_up_to_day2 + amt_day3;
                                    

So if the user decides to update amount_day1, I want all the columns balance_up_to_day… to be updated automatically since there are accrued balance.

If the user wants to change the amount of the column amount_day1 from 100 to 400 for John, I should have the result :

name,amt_day1,bal_up_to_day1,amt_day2,bal_up_to_day2,amt_day3,bal_up_to_day3
John    400,      400,         150,      550,   50,   600
                        

I read about TRIGGER and dynamic SQL, but I still cannot find a way to do it.

Any solution is welcome.

I try: UPDATE accrued_earning SET bal_up_to_day1=amt_day1; The first time the user click.

For the other times, I try UPDATE accrued_earning SET bal_up_to_day2=bal_up_to_day1 + amt_day1;

But that should update the bal_up_to_day.. column dynamically.

• sql
• triggers

• 3 Horrible design. Don't add new columns, insert new rows. – jarlh Commented Jan 30 at 8:19
• 1 Which dbms are you using? Don't make people writing non-compatible answers intended for other dbms:s. – jarlh Commented Jan 30 at 8:36
• 1 A minimal reproducible example is a great start when asking for SQL assistance. – jarlh Commented Jan 30 at 8:37

2 Answers 2

This is a highly nonstandard schema for your table. Is it a requirement to create a new column when the user takes action in your application?

In SQL, it's preferred to maintain a relationship between data. For example, your example above could be modeled by a table with four columns: User, Day, Transaction Amount, Balance To Date.

From there, you could use a SELECT subquery to get the latest "Balance To Date" and use that to create a new row in the table for your user.

For example:

INSERT INTO account_balances (user, day, amount, balance)
SELECT 'username', 
        1, 
        100, 
        COALESCE(SELECT balance 
                 FROM account_balances 
                 WHERE user = 'username' 
                 ORDER BY day DESC 
                 LIMIT 1
                ), 0) + 100;

The COALESCE keyword is to handle the case where it's the first time inserting into the table for a given user.

What you are trying to achieve makes some sense. You have one row per user, and you just want to allow adding more and more data for a given user.

This is a relatively common practice in an object oriented design, where an object User would contain a list of value pairs (one for amount and one for balance). The same design would work perfectly well with JSON objects or NoSQL document oriented database like MongoDB.

Unfortunately this will not work very well in SQL. Indeed:

• in a table, all rows have the same number of columns. So if a user would add two new columns for John, these two new columns would also be created for Marta, Peter, and all the other rows.
• in SQL engines, the maximum number of columns is capped. For SQL server for example it's 1024. Meaning that you'd have 1024, - 1 for the name, /2 because of the value paires, that is 511 possible days. After 2 years your software would be obsolete. For Oracle it's 1000, for PostgreSQL 1600, and 4096 for MariaDB
• Adding columns also have significant performance impact: while one user changes the table structure, no other user can make changes. That's a significant performance bottleneck. Also adding columns potentially affects all the rows further slowing down.

The recommended approach is to use two tables: one for the person , and one for the successive value pairs in the set, using an id to relate each pair uniquely to a person.

id  | name
----|------- 
1   | John
2   | Marta
3   | Peter


id  | day  |  amount  | new_balance
-----------------------------------
1   |  1   |  100     |    100
1   |  2   |  150     |    250
1   |  3   |   50     |    300  

If you want to change a value, you could then easily change the subsequent ones

THis design is still not ideal, because there is a redundancy that could easily lead to inconsistencies (e.g. what if someone would insert 100,110 instead of 100,100 in the first line).

A safer approach would then be to change the operation table to add a sequential number in case several amounts ere recorded for the same day, and remove the balance. The balance can then easily be calculated in a select. If you keep it in the table, better get it updated per trigger only.