Foreign/Primary Keys - Models Architecture for my ATM web app

I’m trying to write an ATM bank machine web app. I’m learning how to use SQL databases with Django and Python which use primary keys and Foreign Keys to establish two “Many-to-One” relationships between different tables.

I’m refactoring my web app by dividing the data points across three different class models (and therefore three different tables in my SQL database). I’ve got a User class, a BankAccount class, and a Transaction class.

For my User class, it is the access_card attribute which will be singular and unique to all the other users in the database. So I’ve designated this as the primary key. Other data points in this table which are not necessarily unique to every entry in the database include the access card’s PIN, when a client opened their account, along with a client’s first and last name.

For the next class that I’ve called BankAccount, it is the account_number that is unique to each instance, so I have designated this one as the primary key. Other attributes in this table which won’t necessarily be unique when data is entered in production include interest amount, account opening date, and transit number.

For the Transaction class, the first two attributes that I declare are the Foreign Keys because they are supposed to refer to the other two classes which have primary keys.

Here is my models.py:

from django.db import models
from datetime import datetime
import decimal
from decimal import Decimal
from random import randint
from django.forms import ModelForm
from telagents.forms import AmountForm
 
class User(models.Model):
   access_card = models.IntegerField(max_length=15, primary_key=True)
   PIN = models.IntegerField(max_length=5 )
   first_name = models.CharField(max_length=30)
   last_name = models.CharField(max_length=30)
   client_since = models.DateTimeField('Client since')
 
   def creation_date(self):
       return self.client_since.strftime("%A %d %B %Y @ %I:%M:%S %p")
  
   def __str__(self):
       return f"{self.first_name} {self.last_name}'s bank account."
  
class BankAccount(models.Model):
   account_number = models.IntegerField(max_length=6, primary_key=True)
   interest = models.DecimalField(max_digits=6, decimal_places=3) # Percent
   account_opened = models.DateTimeField('Inception date')
   transit_number = models.IntegerField(max_length=5)
  
   def __str__(self):
       return f"{self.transit_number} / {self.account_number}"
 
class Transaction(models.Model):
   user = models.ForeignKey(User, on_delete=models.CASCADE)
   account = models.ForeignKey(BankAccount, on_delete=models.CASCADE)
   debit = models.DecimalField(max_digits=12, decimal_places=2)
   credit = models.DecimalField(max_digits=12, decimal_places=2)
   balance = models.DecimalField(max_digits=12, decimal_places=2)
   amount = models.DecimalField(max_digits=12, decimal_places=2)
   trans_timestamp = models.DateTimeField(auto_now=False, auto_now_add=False)
   trans_id = models.BigIntegerField()

What I am trying to do next is perform a lookup to grab all the data from Transactions where account_id == 1 and display those in the table. Here is a screenshot of an sqlite browser showing this table:

As you can see, the transaction app’s table has an account_id column as well as a user_id column which I presume was created by the two Foreign Key attributes declared inside the Transaction class. So does this mean it is working?

Resources I’ve leveraged so far in my exploration of sql, relational databases, and Django docs include:

I’m not sure what changes I need to make inside my views.py to fully extract the data properly and then what changes I need to make in my template to present the data to the web visitor.

Here is my current views.py:

from django.shortcuts import render
from django.http import HttpResponseRedirect,HttpResponse
from .models import BankAccount, User, Transaction
from .forms import AmountForm
 
def index(request):
   balance = 0
   context = {'balance': balance}
   user_data = User.objects.all()
   account_data = BankAccount.objects.all()
   my_transaction_data = Transaction.objects.get(user=1)
   print(request.POST)
   if request.method == 'POST':
       form = AmountForm(request.POST)
       if form.is_valid():
           amount = form.cleaned_data['amount']
           if request.POST['transaction'] == 'Deposit':
               balance = balance + amount
               context.update({'balance': balance,})
           if request.POST['transaction'] == 'Withdraw':
               balance = balance - amount
               context.update({'balance': balance,})
           my_transaction_data.balance = balance
           my_transaction_data.save()
           return render(request, 'telagents/home.html', {'form': form, 'user_data':user_data, 'account_data':account_data, 'context': context,})
   else:
       form = AmountForm()
 
   return render(request, 'telagents/home.html', {'form': form, 'data':data, })

Here is my templates/home.html:

<!DOCTYPE html>
<html lang="en">
 <head>
 </head>
 
<body>
 {% block content %}
 
   <br><br>
 
{% for data_obj in user_data %}
   <center>
   Client Name : {{data_obj.first_name}} {{data_obj.last_name}}
       <br>
   Client Since : {{ data_obj.creation_date }}
       <br>       
 
{% endfor%}
<br>
<hr style="width:5%">
{% for data_obj in account_data %}
       <br>
   Transit Account Number : {{data_obj.transit_number}}
     <br>
   Bank Account Number : {{data_obj.account_number}}
       <br>
   Interest Rate : {{data_obj.interest}} %
       <br>
   Opening Date : {{data_obj.account_opened}}
   </center>
{% endfor%}
<br><br>
 
<center>
<form action="{% url 'index' %}" method="post">
   {% csrf_token %}
   {{ form }}
 <input type="submit" value="Deposit" name="transaction" >
 <input type="submit" value="Withdraw" name="transaction">
</form>
</center>
 
<center>
<table class="GeneratedTable">
  <thead>
   <tr>
     <th>Type</th>
     <th>Timestamp</th>
     <th>Trans ID #</th>
     <th>Merchant</th>
     <th>Debits</th>
     <th>Credits</th>
     <th>Running Balance</th>
   </tr>
 </thead>
  <tbody>
   <tr>
   {% for trans_objs in account_data %}
     <td>(null)</td>           
     <td>{{ trans_objs.trans_timestamp }}</td>     
     <td>{{ trans_objs.trans_id }}</td>     
     <td>Big Bank ATM</td>
     <td>{{ trans_objs.debit }}</td>
     <td>{{ trans_objs.credit }}</td>     
     <td>{{ trans_objs.balance }} </td>
   {% endfor %}    
   </tr>
 </tbody>
  <tbody>
   <tr>
   {% for trans_objs in context %}
     <td>(null)</td>           
     <td>(null)</td>     
     <td>(null)</td>     
     <td>Big Bank ATM</td>
     <td>{{trans_objs.debit}} </td>
     <td>{{trans_objs.credit}} </td>     
     <td>{{ trans_objs.balance }} </td>
   {% endfor %}    
   </tr>
 </tbody>
 
</table>
</center>
 
{% endblock %}
 
</body>
 
</html>
 

Questions for you about your models - Is a BankAccount “owned by” or “belongs to” one (and only one User? If so, then BankAccount should have a ForeignKey to User.

And if the above is true, is there the possibility that a transaction is related to a different User than the user that owns the BankAccount? If not, then you don’t want to associate the User with the Transaction.

Side question: Have you worked your way through the official Django Tutorial? If you haven’t, I would suggest you do so - it exposes you to a lot of concepts regarding querying a database that is going to give you the foundational knowledge to help you understand what it is you need to do here. If you have already done that, then you might want to review part 2 - particularly the examples for “Playing with the API”.

For example:

This works when there is only one transaction for that user. However, once you have multiple transactions, that will throw an error - you need to use filter instead of get to retrieve a queryset instead of a single object.

A very quick observation: is the PIN going to be an arbitrary 5-digit sequence? Are you sure it’s best to store it as an integer? Can a PIN ever begin with one or more zeroes–and if so, what happens by storing an int?

Hi Ken, Thanks for your reply and insight. I completed the official Django tutorial (build a polling web app) a very long time ago, like greater than 2 years. I still have the final source code in a private repo on GitHub but I will redo it from scratch. That’s a good idea.

Thanks for pointing this out. I will use filter instead of get.

For my web app, any number of BankAccounts (say 10) could be owned by and belong to an arbitrarily large number of individual Users (say 10 million hypothetically speaking). Therefore, to best answer your question, perhaps the relationship I need to establish here should be Many-to-Many instead of One-to-Many?

To clarify further what I am trying to achieve, one User could have multiple BankAccounts (but typically just 1). If there are 5 millions unique Users, each one should have their own list of BankAccounts (some might have just 1 Bank Account, while others could have 4 or 10 or even 20). Each BankAccount will have their own ledgers of Transactions.

Each BankAccount should also be capable for performing an arbitirarily large number of Transactions (say thousands). But since this an ATM machine (which I might eventually extend to performing retail / merchant transactions with the User’s unique access card), there would be no scenario where one User’s Transactions would cross post to another User’s Transactions. Users are not going to exchange money (transactions) among each other.

Thank you @samul-1 for sharing your observation. Now that you point it out, for the purposes of my ATM web app, the User’s 5 digit PIN should have a max_length of 5 as well as a min_length of 5. I also gather that if a User chooses their PIN (as an integer) to be, say for example, 00232, then Python would not hold the first two digits and turn it into 232, which is not what I want. I know how to specify sigificant digits to the right of a DecimalField but not to the left of an Integer Field (or even Decimal Field). You are right that storing it as an int as I have it right now will not do. I’ll need to look into this as well.

I would personally just store it as a string. You can validate the value when you clean the field to ensure you’re getting a valid number.

If you want to get fancy, you can write your own PinField, although that’s probably unnecessary

I’m not asking about “BankAccounts” in aggregate.

For any one BankAccount - is it owned by one and only one User? If so, it’s a Many-to-One relationship, with a ForeignKey in the BankAccount model referring to a User

If any BankAccount can be owned by more than one User, then it is a Many-to-many.

It doesn’t matter how many BankAccounts exist in total. You want to evaluate the relationships of an individual entity - one BankAccount in this case.