Structured Query Language/Example Database Structure

First of all a database is a collection of data. These data are organized in tables as shown in the example person. In addition, there are many other kinds of objects in the DBMS: views, functions, procedures, indices, rights and many others. Initially we focus on tables and present four of them. They serve as the foundation for our Wikibook. Other kind of objects will be given later.

We try to keep everything as simple as possible. Nevertheless, this minimalistic set of four tables demonstrates a 1:n as well as a n:m relationship.

person
The person table holds information about fictitious persons; see: Create a simple Table.

contact
The contact table holds information about the contact data of some persons. One could consider to store this contact information in additional columns of the person table: one column for email, one for icq, and so on. We decided against it for some serious reasons. We can deal with all these situations in an uncomplicated way, when the contact data goes to its own table. The only special thing is bringing persons together with their contact data. This task will be managed by the column person_id of table contact. It holds the same value as the Primary Key of the allocated person.
 * Missing values: A lot of people do not have most of those contact values respectively we don't know the values. Hereinafter the table will look like a sparse matrix.
 * Multiplicities: Other people have more than one email address or multiple phone numbers. Shall we define a lot of columns email_1, email_2, ... ? What is the upper limit? Standard SQL does not offer something like an 'array of values' for columns (some implementations do).
 * Future Extensions: Someday, there will be one or more contact types that are unknown today. Then we have to modify the table.

The general statement is that we do have one information unit (person) to which potentially multiple information units of the same type (contact) belongs to. We call this togetherness a relationship - in this case a 1:m relationship (also known as a one to many relationship). Whenever we encounter such a situation, we store the values, which may occur more than once, in a separate table together with the id of the first table.

hobby
People usually pursue one or more hobbies. Concerning multiplicity, we have the same problems as before with contact. So we need a separate table for hobbies.

You may have noticed that there is no column for the corresponding person. Why this? With hobbies, we have an additional problem: It's not just that one person pursues multiple hobbies. At the same time, multiple persons pursue the same hobby.

We call this kind of togetherness a n:m relationship. It can be designed by creating a third table between the two original tables. The third table holds the ids of the first and second table. So one can decide which person pursues which hobby. In our example, this 'table-in-the-middle' is person_hobby and will be defined next.

person_hobby
Every row of the table holds one id from person and one from hobby. This is the technique of how the information of persons and hobbies are joined together.

Visualisation of the Structure
After execution of the above commands, your database should contain four tables (without any data). The tables and their relationship to each other may be visualized in a so-called Entity Relationship Diagram. On the left side there is the 1:n relationship between person and contact and on the right side the n:m relationship between person and hobby with its 'table-in-the-middle' person_hobby.