[TUTORIAL] Building a Database
An important step in the process of building a website or web application is the conception of the data structure. Most of the problems that come up during the development of project come from a poorly conceived database. Bad modeling will create superfluous terms, incapacity and lost data. Good modeling makes the project more flexible for possible improvements. The more robust your model is, the less issues you will run intro when you retrieve data through PHP or ASP. It also makes access to the data faster and simpler.
There are several methodologies to guide you through the modeling of the database.
During my studies, I learned how to build data models based on the Merise method and I think it is a great methodology to follow when you have to build a database for software, websites, or anything else.
Merise is a general modeling methodology that was first introduced in France during the early 1980′s and is still widely used today. The fact that the Merise system relies on logical mathematics makes it really easy to naturally understand. The approaches presented in this post are a part of this methodology.
This post does not have any specific goals but I wanted to show how important it is to build a good database. Through simple examples, I’m going to explain two importants steps in database modeling:
The Conceptual Data Model
As its name implies, a conceptual data model (also called entity-relationship model) is an abstract and conceptual representation of data.
The basics concepts of this data model are:
The Entity
The entity can be defined as an object, actual or abstract (a student, a major, a class, et cetera).
Each entity has attributes. For example if we consider the entity to be a student, the attributes can be the student’s first name, last name, date of birth stylized as:
student_first_name student_last_name student_dob
In order to make sure that each student has his own instance, we have to give each entity what we call a primary Key that will be unique (referred to as the ID). So if two students have the same name, they can still be identified by their specific ID.
The Relationship
A relationship is a semantic link between one or several entities. It can contain one or more attributes. A relationship expressed with a single verb implies direction. Choosing the right verb is always beneficial when generating physical representation from semantic models.
For example a Major contains at least a Class :
Cardinalities
The cardinality of a relationship is the number of entities in the relationship.
We can distinguish 3 types of links between two entities X and Y:
Relationship with cardinality 1.1: each instance of X corresponds with one and only one instance of Y and vice versa. For example, a husband has only one wife and a wife has only one husband.
Relationship with cardinality 1.n: each instance of X corresponds to one or more instances of Y but each instance of Y corresponds to one and only one instance of X. For example, a student can only have one major but a major can have several students.
Relationship with cardinality n.m: each instance of X corresponds with one or several instances of Y and vice versa. For example, a major can contain several classes and classes can appear in several different majors.
Here is an example of a conceptual data model diagram:
The Relational Model
The relational model is the last step before turning your data model into a physical model.
In this step, we’re going to translate each entity, relationship, and cardinality into tables and we’re also going to introduce a new concept: the foreign key.
A foreign key is a field in a relational table (entity) that matches a primary key (ID) of another table (entity). The value of a foreign key is equal to the value of the primary key that it refers to.
Each entity will be a table in this model. Attributes of the entity will become columns of the table.
Now, we have to translate each relationship and cardinality:
Relationships with Cardinality 1.1 Become Foreign Keys :
Relationships with Cardinality 1.n Become Foreign Keys.
One of the two tables receives as a foreign key the primary key of the other table.
The table that contains the foreign key is the one who has the maximum cardinality of 1 (1.1 or 0.1).
The arrow points to the table which gives the foreign keys to the other table:
Relationships with Cardinality n.m Become New Tables.
The name of the new table can be the combination of the names of the two original tables. The primary key of the new table is composed by the primary key of the two original tables.
The attributes of the relationship become columns of the new table:
- Recursive relationships with cardinality 1.n become foreigns keys.
A foreign key is added in the table and this foreign key refers to the primary key of this same table but has a different name.
For example, a student can be the tutor of several students but a student has only one tutor:
The last step before the implementation which will optimize the physical model of the database is database normalization, which I will go further into in a later post.
Technology Tutorials