Weaving Webs With Data

When you log into an app like Instagram, Facebook, or LinkedIn, you’re often either interacting with your own profile or with the profile of others, whether it be through making a new post, liking someone else’s post, or changing your bio for the 5^th time this week. All these things – your username, posts, your followers’ posts, your bio, and more – are pieces of data strung into a web of connections. A current project at DEV involves keeping an SQL (Structured Query Language) database with exactly these bits of data in mind. So how do we fit all these little pieces together?

We can think of a database as a collection of tables, where each table corresponds to a different type of thing we’d like to keep track of. For example, a table for users would include columns for their auto-generated ID, their username, bio, profile picture, or when their account was created. Each row would then represent a single user. It’s important to note here that each column has a single value – you can only have one ID, one username, one bio at a time, etc. Something like a user’s post has its own properties, and users can have several posts, so we make another table for posts themselves, which brings us to the idea of connecting tables together. Our new posts table has some expected columns – a post ID, a like count, a caption, and whatever else might be relevant. But it also has a crucial column: a user ID. In the posts table, we think of this user ID as a foreign key.

Now, with this foreign key, we’re able to match users to their posts by matching the ID in the users table to the user ID in the posts table. The engineering team at DEV can then automatically make this connection, and search for things like a post from a particular user with a particular caption. But we can go even deeper still. Comments are another candidate for their own table, so we can make a comments table with its own columns, as well as both a post ID for the post that the comment was left under, and a user ID for the author of the comment. Then, we can search for all comments left by a particular user, all comments under a particular post, or even all comments left by a particular user under a particular post.

These connections can go as deep as we’d like them to, so long as the data exists. Using the same example, we’re able to look at one user’s post, perhaps with a particular date of creation, then see the comments under this that post, and find all the authors of those comments. Of course, each commentor is a user, so we can look at their posts, the comments under those posts, and so on. With so much depth and flexibility, the engineering team is able to make all sorts of different queries and edits within the database.

A big part of even working with data this way is planning an effective and intuitive structure, making decisions about what belongs as a column or a table, and what types of data are worth keeping track of. This example only shows a tiny portion of what’s possible with SQL databases, but it carries a vital and beautiful message. Without the ability to construct database frameworks and make these different connections within the data itself, we wouldn’t be able to make new posts, know who liked our own posts, nor be able to tell who’s posting pictures of their lunch, again. I like to think about databases as spiderwebs (sorry, arachnophobes): they’re organized strings of connections between different types of data, and there’s always a way to start from one place and get to another. DEV engineers are able to make decisions about how data should connect, and find the most efficient and accessible ways to get from point A in a database to point B.

There’s a certain beauty in working with data, and an incredible satisfaction in being able to find whatever you’d like just by building the right spiderweb. And while by now maybe you’ve thought “So does that make DEV engineers spiders?”, I can promise that we’re not so scary, and perhaps more importantly, we don’t bite!