Database design tutorial
If you are going to create your own database it is good to stick to the rules of database design, because they will ensure the long term integrity and maintainability of your data. This tutorial teaches you what databases are and how to design a database that conforms to the rules of relational database design.
Databases are programs that allow for the storage and retrieval of large quantities of related data. Databases consist of tables that contain data. When creating a database you should think about what tables you are going to create and what relationships exist between the data in your tables. In other words, you have to think about the design of your database. A good database design will ensure the integrity and maintainability of your data.
The structured query language (SQL)
A database is created for the storage and retrieval of data. This means that we want to be able to INSERT data into the database and we want to be able to SELECT data from the database.
A database query language was invented for these tasks called the Structured Query Language, or SQL. Both the SELECT and INSERT operations are part of the Structured Query Language. Below is an example of an SQL SELECT query and its result.
SQL is a large topic in itself, so it is outside the scope of this article. This article focusses strictly on database design. I will discuss the basics of SQL in a separate tutorial later.
The relational model
This tutorial shows you how to create a relational database model. The relational model is a model that describes how to organize data into tables and how to define relationships between these tables.
The rules of the relational model dictate how data should be organized in tables and how tables are related to eachother. Ultimately, this results in a database diagram or Entity-Relationship diagram like the one in this picture. (Example taken from MySQL Workbench)
For the examples in this tutorial I used a number of applications.
First, the database that I used to create example tables is Mysql. Mysql is the most popular database system on earth and it's free (be sure to read the next paragraph on why this is not a Mysql tutorial).
Database administration tool
When you install Mysql you only get a command line interface to operate Mysql. Personally I prefer a graphical tool to administer my database. I often use Sqlyog for managing Mysql. Sqlyog is a freely available graphical administration tool for Mysql. When you see a picture of a database table in this tutorial, it comes from Sqlyog.
Database modelling tool
Mysql also has a great database design tool called Mysql Workbench, which is also freely available. Mysql workbench lets you design your database graphically and gives you the possibility to generate your database right from your design (forward engineering). When you see images of database diagrams in this tutorial (including the one above), they will come from Mysql workbench.
Design is database independent
It is important to know that even though the examples in this tutorial come from Mysql, database design is independent of the underlying database. This means that the information in this article applies to (relational) databases in general, not just Mysql. You can implement a relational database design on any relational database you want, like Mysql, Postgresql, Microsoft Access, Microsoft Sql or Oracle. This is a relational database design tutorial, not a Mysql tutorial!
On the next page I will give you a short overview of the evolution of databases, so you know where databases and the relational model came from.