Relational Database

Relational Database

If my advice on how to date a model didn’t work, then maybe this will, but probably not. Anyway, you probably wouldn’t want to date a database, it will have other relations at the same time. So instead, let’s learn about relational databases.

There are over 80 million WordPress websites currently on the internet. Each WordPress site uses a relational database. This doesn’t include the other millions of websites that run a different content management system or use a custom solution. Most software uses some sort of database, whether directly or indirectly, it usually has to keep/retrieve data from somewhere.

A database is made up of one or more table. Each table is comprised of fields/columns that can hold different types of data. Each row is usually associated with a PK (PRIMARY KEY). PK’s are unique and auto increment with each new row. While you can have several columns in each table, you need to be careful, and make sure you normalize your data.

Example Table

Below is an example table that could hold employee information. This example will be used throughout this post. I kept it relatively little and hopefully it isn’t complicated.

Table 1.0 (Employees)

ID (PK) name phone company
1 Levi 555-1234 Bulldog Creatives
2 John 555-1235 Bulldog Creatives
3 Alex 555-1236 Bulldog Creatives
4 Jake 555-1237 Bulldog Creatives

Do you see anything wrong with the table above? Is there a way you could make it better or save space? What if we needed to fix the typo and change the name of the company from “Bulldog Creatives” to “Bulldog Creative”?

Normalization

The normalization of data helps you keep your information consistent. Since we are human, we are prone to make mistakes. This technique helps reduce the potential risk of making a simple mistake and save you time.

Reference table 1.0, let’s see how we could normalize the data. The company “Bulldog Creative” is repeated four times and if I listed all the employees, it would be a lot more than that. Let’s create another table for companies.

Table 1.1 (Companies)

ID (PK) company_name
1 Bulldog Creative
2 Rose Bud Flowers

We can now alter table 1.0 to incorporate the “Companies” table as shown below.

Table 1.2 (Employees)

ID (PK) name phone company
1 Levi 555-1234 1
2 John 555-1235 1
3 Alex 555-1236 1
4 Jake 555-1237 1
5 Kristal 555-LOVE 2

Now that we have two tables and they relate to each other, we are headed in the right direction. By normalizing our tables, we are reducing data redundancy and improving data integrity. The column “company” FK (Foreign Key) points to the PK of the “Employees” table.

Conclusion

Using this method will help you in the long run. It may even save your butt. With MySQL, you can put restrictions on UPDATE’s with FK’s, which might prevent you from deleting data that is used somewhere else in your database. If you have any questions, feel free to leave a comment below or send me an email.