Database Design - Part 1
When we talk about topics according to the relational model, we refer mainly to the knowledge, at least basic, of the following topics:
- Design and Modeling of the database.
- Design of tables and their relationships.
- Standardization.
- Construction of standardized related tables.
Design and Modeling of Databases
One of the important steps in the construction of an application that manages a database, is precisely the design of the database. If the tables are not defined correctly, we can have problems when executing queries that allow us to obtain the information we want. It does not matter if the database contains only a few records or thousands of them, it is important to ensure that the database It is correctly designed to obtain maximum efficiency and that we can use it for a long time.
Depending on the requirements of the database, the design can be complex, but taking into account some of the simple rules it will be much easier to create a database for each of our projects.
Some of the considerations that we must take into account when performing the design of the database are:
- Access speed
- Size of the information
- Type of information
- Accessibility
- Import and export of information
- Presentation of the information
No matter how good the applications we have developed before, it is always advisable to follow some design standards to ensure maximum efficiency in regard to storage and retrieval of information.
Just as an architect creates the blueprints for the construction of a building, we must create the model for the construction of an application. No architect is encouraged to start the construction of a building without first having a clear idea of what he wants to build, and this idea is modeled with the development of the plans, that's where the dimensions, characteristics and scope of what is it requires building. Well in software development the same thing happens, before starting programming and / or construction as such it is important to create a model that allows us to visualize the characteristics and scope of our application. The model is an abstraction of reality, which it allows us to simulate the final product and clearly understand the objective we are pursuing.
The Entity-Relationship model is one of the main standards that allow us to capture the functionality of the application in terms of entities and the way in which they relate to each other. The entities represent the domain data of our application. For example, if we want to develop an application to take control of tourist tours made by a tourist. The main entities that we find here are: Tourists and Tours, these entities are the data that will be stored in the database. The relationship that these two entities have with each other, could be described as follows: Tourists make tours. The relationship between the two entities is an action, that is, a verb that describes the action that is carried out from one entity to another, which is modeled as shown in the following figure:
The figure describes two entities related to each other according to the entity-relationship model. The additional symbology used in the model allows us to describe in more detail the way in which these two entities are related and we can read it in the following way:
1 Tourist performs N Tours, 1 Tour is made by N Tourists, which results in a many to many relationship (N: M) between the two entities.
In this way we can also understand how the tables that we will build in the database will be. Additionally, in the entity-relationship model, we can specify the necessary attributes for each of the entities, that is, the data that is required to process and identify each of them, for example, for tourists we may require their name, their nationality, their age , etc. For the entity Recorrido, perhaps the route that identifies it, the name of the route or some other data that the company for this entity.
The attributes of the entities will become the fields of the corresponding tables.
Now, if for the "realization" relationship it is necessary to identify when and at what time the journey is made, then the relationship becomes an identified relationship, which would specify two attributes: date and time, according to the example we are discussing .
Design of tables and their relationships
This section will be covered in the second part of this article and basically consists of the transformation of the entity-relationship model to the relational model, in which, concretely, we convert the entities, as well as the relationships identified in tables.
