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.
This will be a blog dedicated to designing relational database indexes. By this I mean we will be discussing the important topics of indexing a database but we will not be programming any SQL. This is all part of database design.
What is an index?
Wow, such as awesome question! An index allows us to select data from our table much quicker. Think of an index in a book…If you want to know something about a specific topic, you check the index.
In order to understand crow’s foot notation, we must first understand the basics of database design. I recommend you read the previous blogs listed to familiarize yourself with all of the database relationship concepts:
Cardinality refers to the relationship between a row of one table and a row of another table. The only two options for cardinality are one or many.
Example: Think of a credit card company that has two tables: a table for the person who gets the card and a table for the card itself. A row from the card_holder table would have a relationship with a row in the card table because the card_holder holds the card.
This will be a quick blog teaching you how to start using MySQL workbench. Although MySQL Workbench is obviously for MySQL, we will be using it to design databases for pretty much any RDBMS. If you have a different modeling program that you prefer, use it! Now that we know the basics of database design we will be really diving into things.
MySQL Workbench is an entity relationship diagram designing program. An entity relationship diagram (ERD) is a visual representation of our database structure. You may also hear Enhanced Entity Relationship Diagram (EERD), Which is an ERD with more functionality. We typically ignore row values and only worry about tables and columns. Now, let’s begin!
In the Introduction to Keys blog we talked about foreign keys. Foreign keys are keys that reference a primary key in another table. Foreign keys are what allows us to store data over multiple tables without having incorrect data. Let’s look at an example to see how a foreign key works.
In my blog about relationships, I explained that we often separate information across multiple tables. This is the beauty of a relational database! The added complexity can take a lot to get used to, but having multiple relations (tables) simplifies data! It removes redundant information and has the capacity to store large amounts of complex data. The use of multiple tables suggests that we need to use keys. A key is a column within each database table that keeps an individual row unique. Not only do keys allow each row to be unique, but they allow us to connect tables by using multiple keys. I’ve used this picture before, but it illustrates things perfectly:
Sorry guys, this is not a blog over dating advise. Rather, it is over database design relationships!
What is a relationship, you may ask? A relationship is the kind of connection an entity has with an attribute and the other way around as well as the connection between multiple tables. Here, lemme’ explain.