‫Welcome back.
‫Now that we mastered the most important
‫architectural building blocks
‫in terms of traffic, API, and content delivery management,
‫it's time for us to talk about databases.
‫When it comes to databases
‫there are so many options to choose from
‫that it's natural for us to feel overwhelmed.
‫So instead of choosing the most popular or trendy database,
‫in the next few lectures we will learn a methodical way
‫on how to choose the right database
‫for our systems use case.
‫In this lecture we'll talk about the first type of database
‫called relational database.
‫We will first learn what relational databases are
‫and what advantages and disadvantages they provide us with,
‫and later we'll talk about when to use a relational database
‫and when we should go the other route.
‫So let's start talking about relational databases.
‫In a relational database data is stored in tables.
‫Each row in the table represents a single record
‫and all the records are related to each other
‫through a predefined set of columns that they all have.
‫Each column in a table has a name, a type,
‫and optionally, a set of constraints.
‫This relationship between all the records inside a table
‫is what gives this type of database
‫the name relational database.
‫Now each record in the table is uniquely identified
‫by what's called a primary key,
‫which can be represented by either one column
‫or a set of columns in the table.
‫The structure of each table is always defined ahead of time
‫and is also referred to as the schema of the table.
‫Because of this predefined schema for each table,
‫which gives us the knowledge
‫of what each record in the table must have,
‫we can use a very robust query language
‫to both analyze and update the data in the table.
‫The industry standard scripting language
‫to perform such queries is called SQL
‫which stands for structured query language.
‫Different relational database implementations
‫have their own additional features
‫in their own version of the language.
‫However, the majority of the standard operations
‫are the same for all relational databases.
‫Relational databases have been around since the 1970s
‫so it's a very well known and proven way
‫of structuring data.
‫One of the biggest advantages of relational databases
‫that gave rise to their popularity so early on
‫was the fact that storage used to be very expensive
‫and storing data in separate tables
‫allows us to eliminate the need for data duplication.
‫Although storage became a lot cheaper since then,
‫the amount of data that collect nowadays
‫is a lot larger than before.
‫So for large scale systems
‫storage costs are still a major factor.
‫Let's see how we can save space
‫and avoid data duplication in a relational database
‫with a small, but very realistic example.
‫Let's assume we have an online store
‫where we sell a limited number of products.
‫We store all the information about those products in a table
‫that contains each product's information
‫like its name, its model year,
‫company, category, and base price.
‫Now as customers place orders for those products
‫we need to store those orders in a separate table.
‫However, in addition,
‫we also want to have the ability
‫to easily analyze and report on things like
‫which products sell the most or the least
‫at a given timeframe,
‫we also may want to rank companies
‫based on their performance of their products,
‫or we may want to get a breakdown
‫of which category of products does better
‫during certain sales or seasons.
‫So an approach would be to have one table
‫where each row contains a single purchase
‫of a particular product
‫as well as all the information about that product
‫that we need to perform all those analytics.
‫Of course, we can already see
‫that this table will take a lot of space
‫because it contains a lot of information
‫on each purchase product.
‫And this table will continuously grow
‫as we get more orders for our products.
‫However, if we look closely,
‫most of the information in this table
‫is duplicated from the products table.
‫So because a relational database allows us to have
‫not only relationships between rows inside a table
‫but also relationships between multiple tables,
‫we can actually avoid this duplication
‫and keep the orders table a lot smaller
‫by containing only the ID of the product
‫that was involved in each purchase.
‫Using this one column that is shared between the two tables
‫we can use the join operation
‫and combine the information from both tables
‫without having to store that information twice anywhere.
‫Now let's talk about the advantages and disadvantages
‫of relational databases.
‫The clear advantage of storing our data
‫in a relational database
‫is the ability to perform those complex
‫and very flexible queries using the SQL language.
‫This allows us to gain deep insight
‫into the data we have about our business or our users.
‫Also, as we mentioned a few moments ago,
‫because of our ability to analyze and join multiple tables
‫we can save a lot of space on storage
‫which directly translates to cost savings for our business.
‫Another advantage of relational databases
‫is that they're very easy to reason about.
‫The reason for that is this table structure
‫is very natural for human beings,
‫and it doesn't require any knowledge
‫about sophisticated data structures and computer science.
‫Finally, relational databases
‫provide us with very powerful properties
‫called ACID transactions
‫where ACID stands for atomicity, consistency,
‫isolation, and durability.
‫In the context of a database,
‫a transaction is a sequence of operations
‫that for an external observer
‫should appear as a single operation.
‫For instance, transferring money
‫from one user's account to another
‫may involve multiple operations internally,
‫but should be perceived as one single operation externally.
‫After all, we don't wanna have a situation
‫even for a brief moment
‫where it appears that the money was withdrawn
‫from the first account
‫but it's still missing on the second account.
‫Or similarly, we don't want to have a situation
‫where the money appears twice
‫in both accounts simultaneously.
‫So to make sure we don't get into either of those situations
‫relational databases guarantee
‫the atomicity of transactions,
‫which means each set of operations
‫that are part of one transaction
‫either appear all at once or don't appear at all.
‫In other words, it's either everything or nothing,
‫but never anything in between.
‫Now let's talk about consistency.
‫Consistency guarantees that a transaction
‫that was already committed
‫will be seen by all future queries and transactions.
‫For example, if we transfer a hundred dollars
‫from user A to user B as a single transaction,
‫once that transaction is complete
‫there is no way that a future query
‫will suddenly see those a hundred dollars
‫either in the first user's account
‫or will not see those funds in the second user's account.
‫But consistency doesn't guarantee only that,
‫it also guarantees that a transaction
‫doesn't violate any constraints that we set for our data.
‫For example, if we set a constraint
‫that a particular user
‫can't have more than a thousand dollars in their account,
‫the consistency guarantee will make sure that a transaction
‫will not leave that user with more money
‫than the allowed amount of money in their account.
‫So if that limit is reached during a transaction,
‫that transaction will simply fail.
‫The next property the relational database guarantees
‫as part ACID transaction semantics is isolation.
‫Isolation is somewhat related to atomicity,
‫but in the context of concurrent operations
‫performed on our database.
‫For example, if we continue with the same example
‫of transferring money from one user's account to another,
‫isolation guarantees that if there's another transaction
‫happening simultaneously,
‫that second concurrent transaction
‫will not see an intermediate state
‫of the money being either present in both accounts
‫or missing in both accounts.
‫So in other words,
‫those two concurrent transactions
‫are isolated from each other in such a way
‫that they do not see each other's intermediate state.
‫The last guarantee that comes from a relational database
‫as part of the asset transaction properties is durability.
‫Durability simply means that once a transaction is complete
‫its final state will persist
‫and remain permanently inside the database.
‫So with this property
‫we will never have a situation where for example
‫a user makes a purchase of a product from our online store
‫and transfers the money to us as part of the transaction
‫but the record about the purchase disappears.
‫So as long as the purchase and the transfer of the money
‫were part of the same transaction
‫and that transaction was completed successfully,
‫the final state of that entire transaction
‫will contain both the record of the purchase
‫and the money transfer.
‫Now that we talked about
‫all the awesome advantages and properties
‫that we get from our relational database,
‫let's talk about its disadvantages and drawbacks.
‫The main disadvantage of using a relational database
‫is its rigid structure enforced by each data table schema.
‫Since the schema applies to all the rows inside a table
‫it has to be defined ahead of time
‫before we can use the table.
‫And if at some point
‫we want to change the schema of the table,
‫for example, by adding a new column
‫or removing a column that we no longer need,
‫we would have to have some maintenance time
‫which results in our table not being available
‫for any operations.
‫So when we design the schema of our tables,
‫we need to be very careful
‫and very thorough in planning ahead
‫so we don't need to change the schema very often
‫or preferably not change it at all.
‫Also relational databases tend to be a bit harder
‫and more costly to maintain and scale
‫because of their complexity.
‫After all, supporting the SQL query language
‫and providing ACID transaction guarantees
‫is not a simple task.
‫Additionally, because of all this complexity
‫and all those guarantees
‫that we get from our relational database,
‫read operations tend to be a bit on the slower side
‫compared to the other type of databases
‫that we're going to talk about in another lecture.
‫Of course, different implementations of relational databases
‫have different performance optimizations and guarantees.
‫But as a general rule,
‫relational databases are notoriously slower
‫than the non relational databases.
‫So now that we talked about
‫both the advantages and disadvantages
‫of a relational database,
‫it's easier to extrapolate that knowledge
‫and see when it's a good idea to use a relational database
‫to store our data.
‫All we need to do is look at the advantages
‫and see if those properties are important to us
‫while the disadvantages can take a backseat.
‫For example,
‫if performing those complex and flexible queries
‫to analyze our data is an important use case for us,
‫or we need to guarantee ACID transactions
‫between different entities in our database,
‫then a relational database is the perfect choice for us.
‫However, if there isn't any inherent relationship
‫between different records
‫that justifies storing our data in tables,
‫or if read performance
‫is the most important quality that we need
‫for providing good user experience,
‫then there are a other better alternatives
‫that we're going to talk about in the next lecture.
‫In this lecture, we started the discussion about databases.
‫We learned about the first type of database
‫called relational databases
‫which is also commonly referred to as SQL databases
‫because of their support for the SQL query language.
‫Then we talked about some of the main advantages
‫that we get from relational databases
‫such as the ability to perform
‫powerful and flexible analysis of our data,
‫efficient storage,
‫which allows us to eliminate data duplication,
‫the natural structure of the data in human readable tables,
‫and the last but definitely not least,
‫the ACID transaction guarantees.
‫After that, we talked about some of the drawbacks
‫of typical relational databases
‫which mainly revolve around the rigid schema
‫that we have to define for each table.
‫This rigid schema increases the complexity of our database
‫and also provides challenges
‫for scalability and performance.
‫In the next lecture,
‫we're going to talk about the other type of databases
‫and what qualities those databases can provide to us
‫that relational databases cannot.
‫See you soon in the next lecture.