should I create a table for every user on buy, cs50 finance

Question

here is my pseudocode for the buy method:

1. if the request method is post:
   1. remember symbol if provided, if not return apology
   2. check if symbol is valid via lookup()
   3. remember shares if it exists and cast it to an int, if not return apology
   4. get cash amount from user
   5. calculate total amount of purchase (total = price * shares)
   6. passed all the conditions? buy it (cash =- total) and update user cash in user table
   7. create a table and add all the necessary info to it
2. if the request method is GET
   1. render buy.html page

my problem is in number 7, so if the same user buy something else again, then am going to create a new table for that user again. Or should I check if the table doesn't exists then create it, but if it does then just insert into it? and if so how would I do that checking? all of this question I'm having the assumption that I need to create the table by code ( CREATE TABLE ...) within the buy function and then inserting to it,,, long story short: what should I do? :) thanks in advance

and here is my function so far:

@app.route("/buy", methods=["GET", "POST"])
@login_required
def buy():
    """Buy shares of stock."""

    # if user reached route via POST (as by submitting a form via POST)
    if request.method == "POST":

        #remember symbol if it exists
        if not request.form.get("symbol"):
            return apology("must provide symbol")
        else:
            symbol = request.form.get("symbol")

        #is the symbol valid?
        q = lookup(symbol)
        if q == None:
            return apology("symbol not correct")

        #remember shares if it exists and casting it to an int
        if not request.form.get("shares"):
            return apology("must provide shares")
        else:
            try:
                shares = int(request.form.get("shares"))

            except:
                return apology("invalid shares")

            if shares <= 0 :
                return apology("invalid shares")

            # get cash amount from users
            rows = db.execute("SELECT cash FROM users WHERE id = :id", id=session["user_id"])
            cash = rows[0]["cash"]

            # calculate total amount of purchase
            total = q[price] * shares

            # not enough cash?:
            if total > cash:
                return apology("no enough mony")



            # passed all the conditions? buy it (cash =- total) and update user cash in user table

            cash -= total
            db.execute("""UPDATE users
                          SET cash = :cash
                          WHERE id = :id""",
                          cash=cash,
                          id=session["user_id"])

            #TODO(7) create a table and add all the necessary info to it

        # secuess (did not reach here yet,)
        return render_template("buy.html")

    # else if user reached route via GET (as by clicking a link or via redirect)
    else:
        return render_template("buy.html")   

Answer 1

I suggest creating a table, called, for example, transactions. In the table, there are columns: user_id, name of the company, quantity of shares, price of 1 share, date. In this way I will keep transactions of all users in 1 table.