Relational Databases Made Simple  ## Metadata - Author: [[YouTube]] - Full Title: Relational Databases Made Simple - Category: #articles - Document Tags: [[database]] [[schema]] [[transactions]] - Summary: Relational Databases Made Simple - URL: https://www.youtube.com/watch?v=0ZF1dPMT_rA [music] If you're building web apps with PHP, you're almost certainly using a relational database. Even if you call it MySQL or Postgress or rarely even think about it, relational databases can one being one of the unseen workh horses of our technology stack. But understanding them to a greater degree will improve your value as a developer and help reduce bugs and unexpected behavior. This video gives a practical introduction to what relational databases are, how they store data, and some of the building blocks you will encounter every day and the guarantees that they try to provide. Hello developers and welcome to the PHP Architect YouTube channel. If this is your first time here, my name is Scott Kekwar. On this channel, we discuss a wide variety of topics related to the PHP ecosystem. Make sure you subscribe so you can get our latest videos as they're published. A relational database organizes data into named tables made of rows and columns and uses relationships between those tables to model complex domain specific information in a structured consistent way. The relational part just means that data is represented in relations which we visualize as tables. Now, as with most technology related topics, there's a lot of vocabulary that we have to keep track of. Database servers contain one or more databases. And databases are just containers to hold various objects like tables, views, and stored procedures. Don't worry if those terms are new. We'll we'll go over them. You can think of a table like a spreadsheet and that it contains columns and rows that can be used to store data. However, a table has a name, a fixed set of columns, but still allows any number of rows as long as we have enough storage for them. Tables group related data. So you might have a users or a posts or a comments table which all contain specific information about the entity that they're built to track. Columns will then define the shape of the data with each column having a name, a type, and an optional constraint. For example, our users table has the columns ID, email name, and created at rows then are individual records within the table. So each row in the users table represents one user and is referenced using something called a primary key. Most relational databases use a textbased query language called structured query language to interact with them. It's an extremely powerful query language and can do anything from creating databases and tables to performing complex retrieval operations. The SQL statement on the screen is what we can use to create our initial users table. Here users is the table with ID, email name and created at as the columns and each inserted record becomes a row. Primary keys and foreign keys are how relational databases express relationships between tables. A primary key uniquely identifies a row within the table. And specific choices are an auto incrementing integer or a universally unique ID. The database will use the primary key internally for indexing and lookups. And we'll use it for referencing specific rows inside of our application. For example, on a lot of sites, you might see a URL that says user one. The one in the URL is most likely the primary key of the user that you're trying to access. A foreign key is a column in one table that references a column, usually the primary key, of another table, and is used to express relationships. Because we have this defined, the database is going to make sure that the values in user ID match an existing ID value in our user's table. And if we delete a user, all of their associated posts will be deleted. And enforcing this foreign key maintains referential integrity, so you don't end up with posts that point to non-existent users. For our PHP application, when you join users and posts, the primary key and the foreign key will provide a fast and consistent way to match rows. Also, knowing where relationships exist help when writing migrations, seeding data, or implementing cascading deletes. Every app builds up technical debt over time. It's the unfortunate price that we pay for shipping new features and moving fast. When we build up too much of it though, it can start to impact how we work. Team velocity suffers. Bugs become more frequent and take longer to fix and everyone starts to get a little bit frustrated. The key to managing debt is to measure it. A credit score can help you understand how well you're managing your debt. And now there's a credit score for technical debt. Go to phpscore.com to get a free technical debt score and monitoring for all of your PHP applications today. Head over to phpsscore.com and get control over your technical debt. One of the acronyms that you're going to hear a lot about is ACID. It's an acronym that describes important guarantees that relational databases provide for transactions. Transactions are just a sequence of operations that should be treated as a single unit and are an extremely powerful part of relational databases. ACID stands for automacy, consistency, isolation, and durability. And each part breaks down as followed. Autom says that a transaction is an all oro thing. If one step fails, the whole transaction is rolled back. So your data is in a consistent state. Consistency says that transactions make sure the database ends up in a valid state at every point during the transaction, respecting all constraints, triggers, and rules as defined by your schema. Isolation says that concurrent transactions won't interfere in other ways that break their correctness. And durability says that once a transaction is committed, its changes persist even if the database crashes. In practice, these guarantees mean that you can perform multi-step changes safely. The most common example is always transferring money between accounts, which requires subtracting from one account and adding it to another. Both steps must succeed or neither should be applied. Now, keep in mind the asset is a model and the exact behavior depends upon the database engine, its configuration, and isolation levels. For most web apps, the default settings provided guarantees that you need. If you started doing distributed transactions or high concurrency financial processing, you will need to dig a little deeper. Data types define what kind of values a column can hold and constraints restrict those values so the database can enforce rules for you. Common database categories include numeric, text, datetime, boolean, binary, and even UUID. Annoyingly, these types aren't always consistent across SQL database variants. So depending on what you're using, you should refer to the database vendor's documentation. Constraints that you might commonly use are not null, which makes sure that a column has a value, unique, which enforces uniqueness across rows, a primary or a foreign key, which defines uniqueness or referential integrity between tables, and then a default value if none is provided. On the screen is an example of a products table that contains some of these constraints and types. Constraints are powerful because the database does the enforcement for you rather than relying solely on application code for validation which often has inconsistent and distributed checks. This leads to inconsistencies where different parts of the system access data. We can define the constraints in the database and it will always enforce them regardless of how the data gets entered. As a brief recap, a relational database organizes data into named tables made of rows and columns. They come by many names including MySQL, Postgress, MSSQL, but they all have the same underlying technology and use. We use types to define the shape of our data and constraints to enforce data consistency. I hope you enjoyed our video. If so, make sure you subscribe, comment, share, and like as it does help others find us. Other topics that you'd like to see us cover, let us know in the comments section below or send me a message on any of the social media platforms listed on my website. We would love to hear how we can help you, and it always brightens my day when I hear from a fan. This is Scott Kekworn for the PHP Architect channel signing off and reminding you to keep watching, keep coding, and keep reading., Relational Databases Made Simple, YouTube, 01kbmqy1825yxe5jj49s7k5aff.