Database Theory
From Morfik Wiki
A Database is a set of records stored in a specific way as to facilitate its retrieval and its usage as personal or business information. Databases are generally managed through the usage of specialized software which, generally known as a Database Management System (DBMS).
There are several architectures that are used to store and manage Databases the most often used is known as the Relational Database and it is the one adopted by Morfik's built-in database.
While most articles in this documentation presume that the user is at least familiar with the general concepts of relational databases such as tables, indices and queries. This is approach takes a very practical approach to databases and focuses on what is used and useful in your everyday work. You will find here the necessary concepts for you to use the database features in the Morfik development environment and in Morfik applications.
Contents |
Tables, Records and Fields
When working with a relational database, data is stored data in relations which are perceived as tables. Tables are composed of tuples or rows or records and attributes or columns or fields.
As you can see relational databases come with their own, ready made, naming confusion. For the sake of clarity we will always use the terms Table, Record and Field in this documentation as these are the most frequently used terms to describe these elements.
All the records within a table contain the same set of fields which are the most basic unit of data stored within the database. Each fields will be of a specific data type, which defines what kind of data can be stored in it.
Primary Key
Every record in a table must be identified by a field that holds a unique value. It is through this value that we will be able to retrieve the record in the future. This particular field is known as the Primary Key of the table. Relational Database theory requires that all tables have a field which serves the purpose of being a primary key, to garantee that any on record can be uniquely identified and retrieved.
A table might contain more than one column that has unique values for each row, for example it might have a numeric id and a telephone number. In this case both columns are what is called candidate keys and you must choose one of them to be the primary key.
One good way to evaluate candidate keys is to think about the possibility that they might change their value over time. For example, it is possible for a person to change phone numbers; in fact it is likely to happen over long periods of time, while a numeric id is under your system's control and won't change unless the system needs it to. Comparing these two candidate keys in this light the numeric id is the better choice as a primary key.
Defining a primary key is a very important aspect of table structure design. In many databases, this task is absolutely necessary as it is the only way to uniquely identify the records in a table.
Foreing Keys
Some fields of a table can store data which represents a reference to some other data which is stored in other tables. Fields which contain such references to other tables are know as the Foreign Keys of the table.
You can have any number of foreign keys in a table.
Relationships
These references establish relationships between the tables which must follow certain rules defined in the type of relationship. There are three kinds of relationships: one-to-one, one-to-many and many-to-many. These relationships are quite self describing in their names but lets look at some examples to better understand them.
In the following figure you can see the representation of two tables one for Sections in a website and another one for Articles.
|
| Figure 1: One to Many Relationship |
In this example the Article table maintains a reference for which Section each Article belongs to as can be seen in the Section Id field of the Article table. The corresponding fields are highlighted. It is clear that in this case an Article can only belong to a single Section, but that a Section can have more than one Article as indicated by records with Article Id equal to 1 and 2 which both reference Section Id equal to 1. This is a One-to-Many relationship.
A Many-to-Many relationship is one in which an article could be in more than one Section at once. In order to represent this relationship we would require a third table to hold the connecting references.
|
| Figure 2: Many to Many Relationship |
In the previous example you can see how an article which refers to the release of a new model of iPod is referenced at the same time by the Music section and by the Electronics section. Also, the first two Articles listed in the Article table refer to the same Section. This is the essence of a Many-to-Many relationship, as one Article can be refered to by many Sections and one Section can contain many Articles.
A One-to-One relationship is much simpler, being essentially represented in the same form as a One-to-Many relationship, whithout allowing duplicate values in the Foreign Key fields of both tables.
See Also