#007 | Backend Database: Build (Part 1)

Overview

I built a database in Xano to store data extracted from the Custodian Statement PDFs. This database replaces the earlier Comma Separate Values ("CSV") file storage format.

I then connected my Flask Python backend to the Xano database using API endpoints, also built in Xano.

I found the Xano learning curve quite steep as A) I was new to it and B) I have very little experience with databases. That said, I managed to achieve my objective with a little trial & error, ChatGPT and help from Xano's tutorial videos.

This article will be split into two parts:

• Part 1: Creating database tables in Xano

• Part 2: Building Xano API endpoints to execute database queries

Step 1: Tables to store entity attributes ("Reference Tables")

I created Reference Tables to store attributes of clients, securities, funds and miscellaneous financial instruments. This process creates a unique Primary Key for each entity, which can be referenced as Foreign Keys in other tables.

The reference_securities table stores a security's full name, short name, stock code, trading currency and stock exchange. I populated reference_securities with data downloaded (legally) from the Singapore Exchange and Bursa Malaysia websites.

reference_funds stores each fund's full name, country of origin, short name and trading currency.

reference_clients stores each client's account number and name. Additional attributes can be added at a later stage, if there is a need.

Step 2: Tables to store Custodian Statements data

I created four tables to store data extracted from the Custodian Statement PDFs.

These tables are linked to one another using Foreign Keys, which are the Primary Keys of fields like a client's account number, security or fund.

The Xano snapshot below summarises the inter-table relationships, along with each table's fields.

Next Steps

With the database tables setup, I now need API endpoints to populate these tables with extracted Custodian Statement data.

The steps I took to build the API endpoints will be outlined in Part 2.

--Ends