Database modeling - JulianPasquale/venmo-api GitHub Wiki

This app uses PostgreSQL database engine. It is a relational database, and here we will explain its structure

Like any relational database, it consists of a collection of tables, and a table has many tuples. A tuple can be mapped to a model of your application if you are using an ORM, but that's another story...

One of the things to notice is the use of Decimal fields. There are many threads about this, but basically you should use decimal if you are dealing with numbers that need to be precise and sum up to correct number (like compounding interests and money-related things). Also notice that in Rails, it maps to a BigDecimal instance, not an ordinary Float.

About the tables:

Payments

This table stores data from payments (unexpected right? 🤣). Each tuple has the sender and receiver foreign keys, to be able to associate a payment with them. To be valid, a payment must have an amount, sender and receiver. You will se that a payment can also have an optional description.

Users

Stores application users' data. Each user has a first_name, last_name and a unique email.

Friendships

As mentioned in the business rules, a user has many friends. A friend is just another user, yo this table has user_id and friend_id foreign keys. This is a N to N relation, so with is just the associative table.

PaymentAccounts

Every user has a balance, and keep this is information is the purpose of this table. The application will do many checks in order to avoid this value to be negative. A payment account belongs to only one user (notice that the balance is a decimal too 😊).