Database Design - Part 4

In the previous article we conclude by defining the structure of the database that we are going to create now in FileMaker and we left the tables in the following way:

However, before building the database in FileMaker, we must consider the use cases of the application.

The use cases describe what the application will do and not as much as it will do, that comes later, for now we need to understand what we will do with the application, how we will use the application and that will also give us an idea of ​​the interfaces of user that we must design, as well as the scheme of operation of the same.

What are the use cases of the application?

Catalogs

The first three cases of use will allow us to maintain the catalogs of the application, these catalogs will be: Nationalities, Tourists and Tourist Routes.

Therefore we must design a presentation that allows the creation and modification of registers for the Nationality, Tour and Tourist tables, as well as a presentation maybe for listings in each of those tables.

In the presentation for the registration of tourists, a portal should be included that allows the creation of related registers towards the Telephone table.

For the Telephone table, it is not necessary to create a presentation, since the navigation in the system will be from the Tourist table, so we can eliminate the presentation that the system creates by default for the Telephone table.

Within the presentation of capture in the Tourist table, in addition to placing a portal for the telephones, we create a list of values ​​for the idNacionalidad field and to that list of values, we assign the nationality field of the Nationality table.

For the Nationality, Tourist and Routes tables, we can create presentations for capture, consultation and listings.

Functionality of tourist routes

The last use case we have called it: "Alta de Recorridos Performed", this is completely different from what the use case about Alta de Recorridos refers, the latter is the management of the touristic catalog offered by the company and the First, it refers to the routes that a tourist makes.

These tours will be the visits made by tourists within the tourist routes, which is why the Realiza table was created, which in reality are the visits carried out on a given date and towards a specific route.

In this table, "Realiza", a record will be kept for each tourist that visits the same route and on the same date, that is, If the route 1 is carried out by 20 tourists, then the table Realiza will have 20 records, one for each tourist, but the 20 records will have the same route and the same date. If we would like to keep a catalog of visits or tours, one for each trip on a specific date, within which there are several tourists, then we must make a change in the design.

At this point of the design and once we start to analyze the use cases, we realize that we need to consider this option, so we commented lines above that it was not wise to start creating the tables in FileMaker.

Realiza will be a visit, so it is required to decompose the relationship between Realiza and Turista, which is a relationship still from many to many, therefore when doing this decomposition we create an intermediate table between these two tables that we will call Tourist in Tour, to which we pass the idTurista of the table Realiza and to achieve the referential integrity we add a idVisita in the table that will be autonomous and that will allow us to establish the relationship with the Tourist in Tour table being as follows:

As we observed in the figure, an additional table called Touristic in Tour was created, which contains two fields: idVisit and idTurist, in addition the table was modified by eliminating the field idTurist and adding a field called idVisit, which will be auto-numeric, To establish the relationship between Realiza and Turista en Recorrido, in this way we can place a portal in the presentation of Realiza to show the related records of the Tourist in Tour table, this relation should allow creating and deleting related records in the Tourist in Travel table .

Now we have the final design of the database, because to create the tables in FileMaker we only have to establish the final considerations for the fields.

The next point is to take into account the considerations for each of the fields in all the tables, as well as the creation of the relationships between tables.

Once the tables have been created (Nationality, Telephone, Tourist, Tour, Performed and Tourist in Tour) with these considerations in the fields, we establish the relationships between them.

For this, we drag from the Telephone table, in this relation the boxes will be activated to create related records and eliminate related records in the Telephone table from the Tourist table, for the following relationship we drag the idTurist field to the idurist field in the Tourist table, the idNationality field from the Tourist table to the idNationality field in the Nationality table.

The idTurist field from the table Realizes to the field idTurist in the Touristic table in Tour, this relation must allow the erasure and the creation of related registers in the Touristic table in Tour from the table Realizes, additionally we drag the number field from the table Realiza to the field number in the Tour table.

Finally we drag the idTurist field of the Tourist table in Tour to the idTurist field in the Tourist table.

The values ​​of the idTurist fields in the tourist table, as well as the number in the Tour table and the idNationality field of the Nationality table will be automatically assigned by the system.

Finally, to register the tourists who make trips, we create a presentation for the table Realiza, in it we establish a list of values ​​for the number field in which we assign the number field of the Tour table, in this way we can choose the touristic tour and show your name in the presentation, for this a portal is not required, simply place the related fields of the Tour table in the presentation of the Realiza table.

Later we placed a portal with the related records of the Tourist in Tour table, although in this portal we put the fields of the Tourist table.

Additionally, if we want to see what tours a tourist has done, then, in the query presentation for the Tourist table we can place a portal assigned to the Realiza table, but in the field we place fields of the Tour table, that way we can have a history of the tours that a tourist has made.