Introducing the demo database - SQL Tutorial

- [Instructor] I considered using existing sample databases, such as MySQL's Employees, SQL Server AdventureWorks, Worldwide Importers, or one of the many others that are out there. It was very tempting to use a familiar off-the-shelf database, but I chose to create a new one from scratch for this series. Honestly, I find most of the existing demo databases to be severely flawed in their design. And I will use this opportunity to demonstrate some of my design choices using a topic that is near and dear to my heart, helping animals in need. So hereby I proudly introduce the Animal Shelter demo database. At first glance, you might find it somewhat odd. First, I chose to use natural, intelligent keys for all tables. There are no magical IDs, auto increment columns, sequences, identity GUIDs, or any other type of surrogate key. This is a controversial topic with near religious opinion and heated debates. And I'm aware that my opinion is in the minority. I find that the abuse of surrogate keys for every table in the database to be one of the most destructive habits ever to plague the relational database world. And surrogate keys have their place and can be useful, just not as a default one-size-fits-all solution for every single table in the database. What's worse is that this practice got so deeply rooted and most database designer these days don't even consider the natural business keys and immediately opt for whatever table name underscore ID as the primary key. And this has devastating results for data consistency, query convolution, workload performance, modularity, scalability, concurrency, and I can go on forever. I plan to release a course dedicated to this topic. For now, I ask that you just take it for what it is, and see for yourself how simple, elegant, and efficient SQL becomes when writing queries against tables that use natural keys. To be able to fit this database in DB Fiddle, I used only a subset of the tables from the full animal shelter database project and with only a few hundred rows of data. If you visit the GitHub project page for this database, you will see that I've included many more reference data tables. These are a must-have for any external authority data. States, cities, zip codes, calendar holidays, species, breeds, and color names are all standardized data sets that must be enforced for consistency. For this course, I included just the colors and species tables as an example in the reference schema. Both consist of a single column, which serves as the primary key. Now, some of you may think it's redundant, and I say it is anything but. Having a standardized reference set of colors and species that every other entity can reference and enforce, will guarantee we will never have misspelled, non-standard, or non-existent values. I created all these tables in the dedicated reference schema, which typically should be in read only so that the application can't mess it up. If reference data is added or modified, a DBA must set it to read write, make the change, and then set it back to read only. We will cover the individual tables as we go along. This project may evolve over time, but the version that is provided in the course materials is guaranteed to produce the same results as you will see in the videos and, as expected, for the challenges.