03 - VFX Database Design

VFX Database Design

TLDR: Python can be used to create and manipulate relational SQL databases to represent all kinds of data involved in VFX production - artists, departments, projects, assets, sequences, shots, tasks, resources, versions, etc.

In order to organize our data and easily keep track of our assets, we will use a relational database. While some smaller studios might only use a directory structure system or a NoSQL database such as MongoDB, good old relational SQL databases are by far the most widely used and arguably most efficient way to manage data in a VFX context. I’m not claiming this is the best road to take, but if it’s good enough for most global VFX powerhouses – it’s good enough for us! Note that throughout this entire guide, I will be focusing on clarity and functionality, rather than hardcore programing efficiency and ultra-fast performance.

Before writing any code, we should consider what our database should look like conceptually and what tables and entities would best represent our data.

Starting from the obvious, our studio has artists, so we’ll need an artist database table. Within the studio, artists are part of different departments. Artists work on projects (also commonly referred to as “shows”). As previously mentioned, projects have sequences, sequences have shots, and shots contain resources created based on certain tasks (e.g. modelling, rigging, animation, etc.). If we have a bunch of resources that don’t exclusively belong to any one single shot, which we want to reuse across many shots and sequences in our project (e.g. a main character’s model and rig), we organize them in assets, which like shots have their own respective tasks. Finally, resources can have many versions, which in turn contain published files. We can further segment our database into more and more database tables for extra control, but we won’t do that for the sake of simplicity. Our database design should already provide us with plenty of data structure to go on.