The Beginner’s Guide To Databases

When people think about scaling an app, rarely does the conversation veer in the direction of databases. How your application’s data is structured will determine the scalability of your application. This decision will always be the responsibility of your development team. However as a PM, knowing what goes into the decision making process can help you be on the same terms with your developers, and also help translate technical jargon over to your stakeholders.

What is a Database (DB)?

When it comes to teaching, I always try to get my students to visualize a concept. The more you visualize, the less it seems like magic.

Heard of MS Excel?

If so, you’re 90% there. Data in a database will almost always look like an Excel doc.

There are a few commonly used databases. Although there are differences between them all, let's focus on the similarities for now.

When you create an Excel doc, you name your columns. The same thing applies when creating a table inside of a database. Some databases refer to these “columns” as “fields”.

Let’s compare an Excel doc to a database:

1. Create new Excel doc → Create new database
2. Create spreadsheet in Excel doc → Add table within database
3. Name spreadsheet → Name table
4. Name column(s) in spreadsheet → Name column(s) or field(s) in table
5. Add data to spreadsheet → Add data to table

Different Types of Databases

There are 2 main types of databases that you’ll hear about:

• 1. SQL - Also referred to as Relational Database Management Systems (RDBMS)
• 2. NoSQL

SQL

SQL (Structured Query Language) databases have structured tables. Those structured tables have predefined columns with data types. Defining a set of columns for a table means that you’ve created a “schema”.

You can set columns in the schema to only allow specific data.

• 1. “price” column can only contain numbers
• 2. “firstName” column can only be alphanumeric with a length of 20

You can always add more columns later, which also means you would update your table schema.

SQL databases may also be referred to as RDBMS (Relational Database Management Systems)

The tables within a SQL database are structured in a way where they form relationships with other tables. These relationships are strictly bound, and must be followed with little flexibility.

The most commonly used ones are:

1. PostgreSQL (”Post-gres-QL”)
2. MySQL (“My-SQL” or “My-Sequel”)
3. Microsoft SQL (”MS-Sequel”)

NoSQL

When people use the term “NoSQL database,” they typically use it to refer to any non-relational database. Some say the term “NoSQL” stands for “non SQL” while others say it stands for “not only SQL.” Either way, most agree that NoSQL databases are databases that store data in a format other than relational tables.

NoSQL data is often viewed in JSON format.

Does NoSQL have schema?

NoSQL databases typically have flexible schemas. Note that some NoSQL databases like MongoDB also have support for schema validation, so developers can lock down their schemas as much or as little as they'd like when they are ready.

What this means is that you can have a table where all of the rows contain different data.

Here are the four main types of NoSQL databases:

• 1. Document databases
• 2. Key-value stores
• 3. Column-oriented databases
• 4. Graph databases

We’ll only be discussing document-based NoSQL databases as those are the most common. But much of the concepts here will apply. The most commonly used services for document-based NoSQL databases are:

1. MongoDB Atlas
2. Amazon DynamoDB
3. Google Cloud Firestore
4. Couchbase Server

SQL vs NoSQL Terms

SQL Data

“users" Table Schema

NoSQL Data

SQL vs NoSQL Uses

Don’t think for a second that there is no one true database that is always best in every scenario. You can build a large-scale application using either.

This is purely opinion-based since there are those that will see things different.

• 1. In SQL, data integrity is usually a higher priority as a result of stricter rules. No rules aren’t always a good thing. Maintaining strong relationships between tables allows for fewer duplicate data. The less duplicate data, the fewer places you will have to update if that need arises. With regards to NoSQL, one document may have the firstName field, but another document may not, which can cause errors in your application, if the codebase was expecting certain data

• 2. In SQL, updating schemas, by adding new columns, can sometimes be difficult

• 3. In NoSQL, querying for data based on IDs is not a problem, but querying for data by any other fields can be pretty inefficient. This is not an issue for SQL databases

• 4. In SQL, creating complex queries that need to combine different tables is much easier and more performant than NoSQL databases

• 5. In NoSQL, writing is generally pretty fast

• 6. In NoSQL, they scale better even if you have multiple connections going to them at the same time

• 7. In NoSQL, really good for storing unstructured data. User settings is one example where this works well. Instead of having a settings table with dozens of columns for each setting, just have a settings collection where you store only the settings of the values that the user has enabled.

SQL

NoSQL

SQL Database Design Examples

Here are some examples of real-world applications using SQL databases, and relational design principles.

Chat App - https://www.linkedin.com/posts/zeeshansyed1_chat-db-design-activity-6914211116814381056-wPOU/

eCommerce Store - https://www.linkedin.com/posts/zeeshansyed1_ecommerce-store-db-design-activity-6917104161603923968-i9L5

Ride Sharing - https://www.linkedin.com/posts/zeeshansyed1_rideshare-db-design-activity-6915291842137755648-v90K/

Task Management - https://www.linkedin.com/posts/zeeshansyed1_task-management-db-design-activity-6914568610636029952-T1ox

Conclusion

Here’s the important take-away here. There’s no 1 right answer regarding your chose of database. Different scenarios will call for different databases. In a large company, you’ll likely see both SQL and NoSQL databases being used.

Although PMs do NOT need to know how to code, understanding technical jargon is vital when working with a development team. It’s not uncommon for PMs to feel overwhelmed with buzzwords and fancy jargon.

So take the time to understand the important ones. Database design is definitely one of them as the terminology used here will be regularly mentioned by developers. At the same time, database design is where building for scale actually begins.

If you enjoy digestible and practical tech content for PMs, follow me on LinkedIn.

‍