Relational database characteristics
Relational databases are designed for fast storage and retrieval of large quantities of data. Below are some of the characteristics of relational databases and the relational model.
The use of keys
Each row of data in a table is identified by a unique "key", called the primary key. The primary key is often an automatically incrementing number like 1, 2, 3 4.... etc. Data in different tables can be linked together using keys. The primary key values of one table can be added to rows of a different table, thereby linking those rows together.
Using the Structured Query Language (SQL), data that from different tables that are linked by keys can be selected at once. You might for example create a query that selects all orders from a orders table that belong to the user with id 3 (mike!) from the users table. We will talk a lot more about keys later in this article.
The id column of this table is the 'primary key'. Each record has a unique primary key, often a number. The usergroup column is a 'foreign key' column. Judging from its name it probably references a table that contains user groups.
Avoiding data redundancy
In a database design that adheres to the rules of the relational model, each data item, a username for example, is stored only once, that is, in one location. This avoids having to maintain the same data in multiple locations. The duplication of data is called data redundancy and this should be avoided in a good database design.
Constraining the input
Using a relational database you can specify what sort of data a database column is allowed to contain. You can create fields that contain numbers, decimal numbers, small texts, large texts, dates, etc.
When defining a database table you supply a type for each column. 'Varchar' is the mysql data type for a short text of max 255 characters and 'int' is a number.
Besides data types, database systems allow you to apply further constraints like length constraints and like enforcing the uniqueness of a certain field. The unique constraint is often used for fields that contain usernames and email addresses.
These constraints give you control over the integrity of your data. They prevent situations like
- entering an address (text) in a field where you were expecting a number
- entering a zip code of one hundred characters
- ending up with two users with the same username
- ending up with two users with the same email address
- entering a weight (number) in a birthday (date) field
Maintaining data integrity
By setting field properties, by linking tables and by setting constraints you can increase the reliability of your data.
Most relational database systems offer a rights structure with which rights can be assigned to different users. Some of the operations that can be allowed or disallowed to a user are SELECT, INSERT, DELETE, ALTER, CREATE, etc. These rights correspond to the operations that can be performed using the Structured Query Language (SQL).
Structured Query Language (SQL)
In order to actually perform operations on the database, like storing new data, and selecting and altering existing data, SQL queries are used. The Structured Query Language is relatively easy to understand and it allows advanced database operations, such as the selection of linked data from multiple tables with JOIN queries. As previously discussed, SQL is out of scope for this article. I will discuss SQL in a separate article. I will focus strictly on database design in this tutorial.
The way you design your database has a direct effect on the queries you need to write to retrieve data. That is another reason why it is good to think about how you design your database. With a well designed database you can write cleaner and easier SQL queries.
An SQL select query that selects the entire contents of the USER table.
The relational model is a standard. By adhering to the rules of the relational model you ensure that your data can be transfered between relational database systems relatively easily.
As stated said before, database design is about identifying the relationships in your data and putting those on paper (or in a computer model). Database design is independent of the database system you are going to use to implement your database. On the next page we will delve deeper into the the use of primary keys.