MySQL – Basic Design Concepts

MySQL is a relational database management system and when used properly becomes a great tool for storing and accessing data; however when designed and implemented improperly can cause many issues.

Recently while being introduced to a new project I noticed the database structure was practically non-existent leaving me blocked in continuing work. I had finished the front-end views and was able to start all of the back-end work or so I thought…..

Whomever setup the database did not appear to understand the most basic concepts of database design. So here we are. Lets take a simple approach to this.

Create Database

mysql> create database fake_company;
Query OK, 1 row affected (0.00 sec)

Switch to the Database

mysql> use fake_company;
Database changed

Create two tables (so we can show relationships)

mysql> create table employee ( id int auto_increment, first_name varchar(100), last_name varchar(100), primary key(id) );
Query OK, 0 rows affected (0.42 sec)
mysql> create table salary ( id int auto_increment, regarding int, salary varchar(20), primary key(id) );
Query OK, 0 rows affected (0.33 sec)

Add some seed data

mysql> insert into employee set first_name = 'John', last_name = 'Smith';
Query OK, 1 row affected (0.08 sec)
mysql> insert into employee set first_name = 'Jack', last_name = 'Jones';
Query OK, 1 row affected (0.08 sec)
mysql> insert into employee set first_name = 'Nancy', last_name = 'Gonzales';
Query OK, 1 row affected (0.11 sec)
mysql> insert into salary set regarding = 1, salary = '$40,000';
Query OK, 1 row affected (0.05 sec)
mysql> insert into salary set regarding = 2, salary = '$45,000';
Query OK, 1 row affected (0.07 sec)
mysql> insert into salary set regarding = 3, salary = '$49,000';
Query OK, 1 row affected (0.04 sec)

Now that we have a database with some sample data we can see the power of relationships. Lets see what we can do with this data by taking a look at a simple LEFT JOIN:

+------------+-----------+---------+
| first_name | last_name | salary  |
+------------+-----------+---------+
| John       | Smith     | $40,000 |
| Jack       | Jones     | $45,000 |
| Nancy      | Gonzales  | $49,000 |
+------------+-----------+---------+
3 rows in set (0.00 sec)

So the employee id’s in the employee’s table id column are related to the salary table through the regarding column. Other notes:

1. NEVER use multiple databases with only one table – this is not why relational databases were created.

Anyways hope you learned something today and have a wonderful day world!

Write a Comment

Your email address will not be published. Required fields are marked *