SQL Needed Structure

Summary

Here are two pages from the internet movie database: There are two things to note about these pages. The data on the page is presented in a hierarchichal structure. The movie page contains a director, a list of genres, a list of actors, and each actor in the list contains a list of characters they played in the movie. You can't sensibly fit all of this into a single flat structure like a relation. The order of the hierarchy isn't the same on both pages. On one page we have movie->actors and on the other page we have actor->movies. So you can't just directly store the hierarchy in your database - you need to be able to traverse relationships in both directions. So we store all our data in a relational database in flat tables and then whenever we need to render some UI we transform the flat data into whatever hierarchy we need. Doing this transformation by hand is tedious and error-prone. We call this tedium "the object-relational mismatch" but it isn't really about objects or relations. The fundamental problem is that fitting complex relationships to human vision usually requires constructing some visual hierarchy, but different tasks require different hierarchies. Whatever database and programming language you use, you will have to deal with this. But it's particularly painful in sql because sql wasn't designed to produce hierarchical data. sql wasn't built to yield structure Let's grab the imdb public dataset and try to reproduce the source data for that movie page (or at least a subset of it, because I didn't bother importing all the tables). We want to see an output that looks like this: { "title": "Baby Driver", "director": ["Edgar Wright"], "writer": ["Edgar Wright"] "genres": ["Action", "Crime", "Drama"], "actors": [ {"name": "Ansel Elgort", "characters": ["Baby"]}, {"name": "Jon Bernthal", "characters": ["Griff"]}, {"name": "Jon Hamm", "characters": ["Buddy"]}, {"name": "Eiza González", "characters": ["Darling"]}, {"name": "Micah Howard", "characters": ["Bari...