Data model

Design

The next diagram shows the theoretical vision of the data model, in which the best practices related with the normalisation of the data have been followed. However, the implementation contains a change that contravenes these norms: the original primary keys of all the entities have been replaced by numerical identifiers acting as keys. The reasons of this change are, on the one hand, efficiency, avoiding the keys formed by multiple attributes. On the other hand, convenience, since these numerical keys can be autogenerated by the database. Besides, numeric ids can be used to keep a reference between database objects and external objects, for example, objects in a client application.

To describe in brief the model of data, we will mention the most important entities and relations:

  • The entity task could consider the main object of the application, since the main purpose of PhpReport is to store, classify and extract information about tasks. It has important relations with user and project, that serve to classify the task.
  • The entity user is used mainly to control the access to the application, besides classifying tasks. There is a series of entities related with user that provide valuable information when they combine : the relations with event, city_history, journey_history do calculations on the working time, and hour_cost_history on the cost.
  • The entity project is used to classify and group tasks. It is interesting to mention the relation called works with user, which could be used to restrict the access to certain information in the application.
  • The entity customer classifies projects.
  • The entity sector classifies customers.
  • The entity area classifies projects and users. The relation between areas and users happens through the entity area_history which stores the history of that user belonging to areas.
  • The entity group defines levels of access in the application.
  • The entity extra_hour is used to reset the calculation of extra hours of an user in a determinate date.
  • The entity event provides basic support for work calendars. It stores a list of dates which are considered non work days in a certain city.
  • The entity city represents the city to which the worker is or has been assigned, through city_history.

There are entities related with unimplemented features:

  • Project_schedule to define project weekly schedules.
  • Custom events to define personal work calendars, independently from per-city calendars.