As many of you know, my technical roots are in databases, SQL, and full stack web development.
I’ve spent a lot of time creating videos on YouTube over databases. For example, I have a 51 part video series on Database Design.
Who or What is freeCodeCamp?
Recently I was reached out to by freeCodeCamp. For those who don’t know, freeCodeCamp is one of the most popular online learning platforms for software development. freeCodeCamp is a nonprofit organization that brings people from noob to developer through their online curriculum.
freeCodeCamp came to me to talk about potential collaborations. I’m happy to say that we came to agreements and decided that we would aggregate my database design series into one long, undivided video! As a result, the Database Design Full Course was born.
Time series for Database Design Full Course:
⌨️ (0:00:00) Introduction
⌨️ (0:03:12) What is a Database?
⌨️ (0:11:04) What is a Relational Database?
⌨️ (0:23:42) RDBMS
⌨️ (0:37:32) Introduction to SQL
⌨️ (0:44:01) Naming Conventions
⌨️ (0:47:16) What is Database Design?
⌨️ (1:00:26) Data Integrity
⌨️ (1:13:28) Database Terms
⌨️ (1:28:28) More Database Terms
⌨️ (1:38:46) Atomic Values
⌨️ (1:44:25) Relationships
⌨️ (1:50:35) One-to-One Relationships
⌨️ (1:53:45) One-to-Many Relationships
⌨️ (1:57:50) Many-to-Many Relationships
⌨️ (2:02:24) Designing One-to-One Relationships
⌨️ (2:13:40) Designing One-to-Many Relationships
⌨️ (2:23:50) Parent Tables and Child Tables
⌨️ (2:30:42) Designing Many-to-Many Relationships
⌨️ (2:46:23) Summary of Relationships
⌨️ (2:54:42) Introduction to Keys
⌨️ (3:07:24) Primary Key Index
⌨️ (3:13:42) Look up Table
⌨️ (3:30:19) Super key and Candidate Key
⌨️ (3:48:59) Primary Key and Alternate Key
⌨️ (3:56:34) Surrogate Key and Natural Key
⌨️ (4:03:43) Should I use Surrogate Keys or Natural Keys?
⌨️ (4:13:07) Foreign Key
⌨️ (4:25:15) NOT NULL Foreign Key
⌨️ (4:38:17) Foreign Key Constraints
⌨️ (4:49:50) Simple Key, Composite Key, Compound Key
⌨️ (5:01:54) Review and Key Points….HA GET IT? KEY points!
⌨️ (5:10:28) Introduction to Entity Relationship Modeling
⌨️ (5:17:34) Cardinality
⌨️ (5:24:41) Modality
⌨️ (5:35:14) Introduction to Database Normalization
⌨️ (5:39:48) 1NF (First Normal Form of Database Normalization)
⌨️ (5:46:34) 2NF (Second Normal Form of Database Normalization)
⌨️ (5:55:00) 3NF (Third Normal Form of Database Normalization)
⌨️ (6:01:12) Indexes (Clustered, Non-clustered, Composite Index)
⌨️ (6:14:36) Data Types
⌨️ (6:25:55) Introduction to Joins
⌨️ (6:39:23) Inner Join
⌨️ (6:54:48) Inner Join on 3 Tables
⌨️ (7:07:41) Inner Join on 3 Tables (Example)
⌨️ (7:23:53) Introduction to Outer Joins
⌨️ (7:29:46) Right Outer Join
⌨️ (7:35:33) JOIN with NOT NULL Columns
⌨️ (7:42:40) Outer Join Across 3 Tables
⌨️ (7:48:24) Alias
⌨️ (7:52:13) Self Join
What to Expect From this Course?
This course, being eight hours long, is obviously quite a large commitment. In all honesty, though, the time will be well invested. This course will be relevant as long as SQL is a thing. It focuses on the fundamentals that make you a better developer. By the end of this course, you will understand the foundation of databases including keys, relationships, normal forms, and joins.
Where do Databases Come in?
The reality is that databases power nearly all applications. Every fancy website and awesome app will likely interface with a database at some point. Databases are how we store information about you, your preferences and settings, app activity, etc. SQL (structured query language, the language used to talk to a database) is one of the most popular languages and is often used in parallel with other programming languages. This means that even if you end up focusing on C++, C#, or Python (as examples), you still need to know how to work with a database.
Database Design for the Full Stack Developer
A lot of full stack developers underestimate the skills required in database design and development. As a result, they introduce serious issues into the application like bad design, untrustworthy data, and security flaws. This course would be ideal for the full stack developer because it looks at databases at the core level. What are they? Why are they useful? How do we design them? How do we aggregate data from multiple tables using joins?
Obviously this course is useful for more than just the full stack developer, but I’d highly recommend it if you are one or hope to be one 🙂.
The core of database design is understanding integrity. Data integrity at it’s core is simply being able to trust our data. This means that data is correct, there is no duplicate data, and we can expect it to stay that way. Data integrity is why we use a database like MySQL, SQLServer, Oracle, Db2, SQLite, etc. Because with a database, we can set rules that force integrity.
The types of integrity are:
- Entity integrity – Each row has a unique ID or something similar to enforce uniqueness. This is known as the primary key.
- Data integrity – The values for a particular column are legit thanks to data types or constraints.
- Referential integrity – Related tables are connected through references.
In order to protect our data’s integrity, we must use multiple tables. You’ll see why in this course. As a result, we need to figure out how to say these tables are related. This is done with foreign keys which are foundational for referential integrity. When we retrieve data from our database, we can bring data from multiple tables in to one master view using joins.
Relationships describe the various ways data can be related. For example, a class can be taught by multiple professors and one professor can teach multiple classes. This is an example of a many-to-many relationship. Each type of relationship has a specific way to be designed in a database. This course will clearly describe how to design the three types of relationships: one-to-one, one-to-many, and many-to-many.
Once we have the foundation of database relationships, we begin to look at database normalization. This consists of multiple normal forms, which are essentially a series of rules that, if broken, throw our data integrity into question. The first three normal forms are the most important and consist of the first normal form (1NF), second normal form (2NF), and third normal form (3NF). Those names are easy enough to remember.
A big section that a lot of database design courses leave out or avoid is the section on joins. It’s easy to break data up into multiple tables, but nobody wants to explain how to put it back together when you need to retrieve data from your database to use in an application. The easiest way to aggregate data from multiple tables into one view is through the use of joins. There are numerous types of joins and they all have different rules on what rows are valid to be added to the view and what rows are not based on a join condition. Typically the join condition will associate a foreign key to a primary key of another table (we talk about all of this in the course).
I encourage you to watch the course. Heck, why not do it one sitting? It could be done in one work day!
I see more collaborations with freeCodeCamp or Quincy Larson (the founder of freeCodeCamp) in the future so stick around! In the meantime, why don’t you leave a comment saying what you would like to see between me and freeCodeCamp?