Finance Project 5: The data model

Now that I’ve got Django up and running I can create my models.

So, getting straight in to the code, I’ll start with the account hierarchy. In finance/models.py:

from django.db import models

class AccountType(models.Model):
    name = models.CharField(max_length=20)
    
    def __str__(self):
        return self.name

class AccountCategory(models.Model):
    name = models.CharField(max_length=50)
    account_type = models.ForeignKey(AccountType, on_delete=models.PROTECT)

    def __str__(self):
        return self.name

# Set plural name for use in the admin site - otherwise it's "Account categorys"
    class Meta:
        verbose_name_plural = "Account categories"

class Account(models.Model):
    name = models.CharField(max_length=50)
    account_category = models.ForeignKey(AccountCategory, on_delete=models.PROTECT)

    def __str__(self):
      return self.name

I can now add those fields to the database:

$ python manage.py makemigrations finance
Migrations for 'finance':
  finance/migrations/0001_initial.py
    - Create model Account
    - Create model AccountCategory
    - Create model AccountType
    - Add field account_type to accountcategory
    - Add field account_category to account

$ python manage.py migrate

So what’s going on here? Most of it’s straightforward, and you can see how the object definitions map to the database fields that Django will create.

The __str__(self) function is a helper to return a useful text representation of the object. It’s mostly used in the shell and automatic admin functions.

Now I can round out the models with the transaction and entry objects. I’ve given the transaction object two date fields – an automatic one that will populate when the object is created and a manual one for the actual transaction. That gives me the audit trail of seeing that I didn’t get round to entering a transaction until some days after it occurred.

For the entry table I had to make a decision about storing numbers. My first thought was a decimal field holding two decimal places as that’s the obvious mapping to the real world. It is, however, common practice to use integer fields to store financial data at the lowest unit of the currency – i.e. pennies in my case. It’s more efficient for the computer’s calculations and avoids the risk of floating point errors.

Those are minor concerns for me, given the way I’ll be using the system, but I might as well follow best practices where I can. It does mean a certain amount of manipulation when displaying and entering data, but it should be possible to handle that easily enough.

class Transaction(models.Model):
    entry_date = models.DateTimeField(auto_now=True)
    transaction_date = models.DateTimeField('date')
    description = models.CharField(max_length=100)

    def __str__(self):
        return self.transaction_date

class Entry(models.Model):
    transaction = models.ForeignKey(Transaction, on_delete=models.PROTECT)
    account = models.ForeignKey(Account, on_delete=models.PROTECT)
    amount = models.IntegerField()
    description = models.CharField(max_length=100)

    def __str__(self):
        return self.amount

No doubt I’ll be adding additional fields as I go along, but that’s enough to get started. It’s worth a quick word on my choice of PROTECT for on_delete.

You’ll often see on_delete=models.CASCADE in Django examples. That means that a delete of an object that is referenced by other objects will also delete those objects. Cascading the deletes maintains referential integrity – it’s impossible for an object to reference another object that no longer exists.

For example, if I were to delete an account type then it will delete any account categories within that type. In turn that will delete any accounts that refer to those categories and any entries that refer to those accounts.

Such a cascade would break the double entry integrity, as it would leave the transaction and the entry that refers to the other side of the double entry.

It’s safer to use on_delete=models.PROTECT. That ensures that I can’t delete an account type if there are account categories that refer to it. I have to make sure those categories are safely deleted first, and so on down the chain. All I’m doing at the database level is ensuring that the database referential integrity is maintained.

At this stage I’m still not sure how I’m going to manage the requirement that entries within a transaction balance to zero, and clearly that has an impact on how individual entries are deleted. I may revisit the model later to build some of that logic into model level constraints if it feels like the right place to do it. I’m feeling my way into this and as I learn more about Django I may discover better ways to manage the data.

in Finance Project

Related Posts