Database schema - mfragkoulis/aftognosia GitHub Wiki

The Aftognosia system's data will be managed by a relational database management system. Each relational table's primary key is displayed in bold font.

We take into account that some data elements modelled in the database schema below may already be recorded by other systems. For instance, the relation Employee is recorded by the Apografi system as explained in this announcement. However, given that the functional requirements include a) providing a past view of the public sector and b) comparing two snapshots we will autonomously store all entities described below unless these functional requirements are also met in the other systems. If they are met, we will not store the corresponding data, but access them by querying the other system. If they are not met, we will ensure that our data are up to date with data of other systems, e.g. Apografi for the employee entity, by requesting to receive updates as they happen; master-slave database replication happens in a related fashion. In any case, because we have no access to other systems to begin with, we will start with storing all data elements. Nevertheless, it is useful to know what primary keys other systems use for entities so that interfacing with them later is as effortless as possible. For instance, Apografi uses the Employee's tax id as primary key.

One of the core capabilities of Aftognosia that is also explicitly stated in the use cases is to retrieve a snapshot of the structure and staffing of the public sector at a specific point in time. The database schema incorporates this capability using the ValidFromTimestamp (VFT) and ValidUntilTimestamp (VUT) attributes.

Unit (UnitId, UnitDesignation, VFT, VUT)

UnitStructure (UnitId, SupervisingUnitId, VFT, VUT)

Employee (EmployeeId, FirstName, LastName, FatherName, MotherName, BirthDate, TaxId, VFT, VUT)

UnitEmployee (UnitId, EmployeeId, AssignmentId, VFT, VUT)

Expense (ExpenseId, OccuredOnDate, InsertedAtTimestamp, UnitId, EmployeeId, ExpenseDesignation, Amount)