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
The relational model allows for the linking of data from different tables using keys. Using the Structured Query Language (SQL), linked data from multiple tables can be selected at once with so called JOIN queries. 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 (username or email address for example) across all rows.
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 applying data integrity rules you can increase the reliability of your data.
Rights
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.
An SQL select query that select the entire contents of the USER table.
Portability
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 a 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.