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:

  • Open a blank sql file and put all your sql commands there, save it and feed it directly to the psql prompt after creating the required database like so: psql employee_sti < sql.sql. Where employee_sti is the db name I had created earlier using createdb command.
  • Design tables that you think might be needed to represent the required data in a structured format. Just in this very step you’ll be forced to think through things like CHECK and NOT NULL constraints, and the datatypes and their size in memory etc.
  • Insert some data
  • Now’s the best part. Think about the usecase of the app in terms of data retrieval. What are all the pages in the app you’d need the data from this db, and how you’d need them?
  • Write insert/select/update queries that are efficient, that do the job in as few queries as possible. Here’s where the native features of a database engine shine a lot. You could use a lot of advanced features to write your query both effectively and concisely. See if you can use features like window functionsm, common table expressions, recursive and lateral queries etc.

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
  • manager
  • lead
  • engineer

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

  • Create a rest endpoint to print hierarchy given any position in the above mentioned organization (Direction: bottom to top).
  • Top 10 employees with ratio of salary
  • Delete an employee from the company. Before deleting though, assign his reporter as reporter for all of his reportees.

Possible Design

Attrs

  • id
  • name
  • role in (bossman, manager, lead, engineer)
  • reporter_id

Relations

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

Testcases

  • x.reporter
  • x.reportees
  • x.superiors and x.subordinates
  • ceo.reporter.nil? == true
  • (roles - ceo).each { role role.reporter.present? == true }
  • sde.reportees.empty? == true
  • (roles - sde).each { role role.reportees.present? == true }
boss Joe, 120, 60%
manager Joe, 40, 20%
lead Joe, 30, 15%
eng Joe, 10, 5%

And the videos!

Part 1 - Sql first design

{{< youtube flmwyY3l_uI >}}

Part 2 - Code first design (rails)

{{< youtube MpvlW8aVLNM >}}