Prasanna Natarajan

Database driven development

Ever since I read Mastering PostgreSQL by Dimitri Fontaine, I’m hung up with this idea that to design an app, start at the database first. Derek Sivers too said something similar about moving business logic mostly into database.

These days, to start a greefield project, most young people start off creating an web app using frameworks like rails or django. But the database is the foundation of any apps and you want to make sure that it’s designed properly.

Any initial design would go through many rounds of redesigns. A rails app would make you slow during those initial stages where you are architecting your app’s models. It’s easy and fast to do this step right in your database.

My preferred choice is postgresql and the author argues this point in the book well.

Here’s the rough sequence of steps to design from db first:

Here’s a problem that I came across, mostly an interview question, that I solved using this database-driven-development approach.

I’ve made 2 videos - one showing the ddd approach and the other showing the rails way (scroll to bottom).

Both the sql file and the rails app are in github: https://github.com/npras/employee

The problem

A company has various roles.

Boss doesn’t report to anybody. Apart from Boss every other employee reports to someone else. Engineer is not allowed to have reportee under them. Apart from Engineer role every other role can have multiple reportee.

Tasks


Possible Design

Attrs

Relations

1 employee has_one reporter (but ceo has no reporter) 1 employee has_many reportees (but engineers have no reportees)

Testcases

boss Joe, 120, 60%
manager Joe, 40, 20%
lead Joe, 30, 15%
eng Joe, 10, 5%

And the videos!

Part 1 - Sql first design

Part 2 - Code first design (rails)