POD Configuration Database

POD (Portal Open Data) is a project that I started when I was studying to obtain Microsoft Web Developer MCSD, in order to do some practices. Consists of a SQL Server database through which on can access to other databases in a centralized way, the target was to develop around it samples of WCF, Web API and Open Data services and Entity Framework, LINQ to SQL and so on data access.

There are some classes specialized in access data, process it and return a single CSV data table and a complete desktop application that uses them to query and process the data. The project itself wouldn't have a special interest, but I used it to include the PISA database, which we will see in other articles and that will serve to do some practices of data analytics, so we must install it. In this link, you can download a copy of the POD configuration database.

By the moment, there is no application to manage this database, but it comes with the configuration to access the PISA database, so, in principle, there is no need to do anything but download and install it in a local SQL Server. Once installed, you must also download and install the PISA database.

Next, for those that are interested, I will make a brief introduction to the most relevant database structure, although reading it is not necessary in order to download and install the database.

Description of the most important tables

We can commence by defining the data access. First we must insert a register in the Bases_de_datos (databases) table.

The nombre (name) field is for a friendly name to show the user, and the esquema (schema) field is for indicate the name of the corresponding SQL Server database name.

The different entities used to filter the data are defined by using the following tables:

The data entities are, for example, years, countries, etc. These entities are used to filter the queries to the database, for example, in PISA you can query for the answers to a particular question in a particular country and year. Responses, years and countries are entities. You can view a entity as a table, and each of the registers on the table are an entity value. For each entity there is a register in the Entidades (entities) table.

id_entidad is simply an autoincremental key, nombre_bd is a foreign key to Bases_de_datos table, to indicate in which database is located the entity, identificador_interfaz is an entity identifier to refer the entity in the queries. For example, in PISA database, the years are identified with the A letter, and a filter for a query can contain a string like A(2000,2003) to filter the data by year.

id_tipo_valor identifies the data type of the entity key. It can be N for numeric values or A to indicate alphanumeric values. This value refers to the register key datatype of the entity table, for example, the years are numeric values because the key of the year table is the year itself.

The valores_hijos (child values) field indicates if there are child values for the entity, for example, the questions entity has a child that is the responses entity, and this one has a child that is the response values.

The resumen (summary) and orden_resumen (summary order) fields are used to indicate if the entity is shown in the summary that the application shows to user with your filter selection, and the order in which it appears in that summary.

This is the internal representation of the entities, but also is necessary to show them to user in a friendly way, either through a web interface or a desktop application. To do so, there is the Traducciones_Entidades (entity translations) table, which allows define translations in different languages for the names and descriptions of the entities.

id_traduccion is the table autoincremental key, and id_entidad is the foreign key to the entity on which the translation applies, whose language is defined in the idioma (language) field.

The traduccion (translation) field contains the translation of the name of the entity, and the descripcion (description) field contains a longer text whit a summary of the entity meaning or function.

The rest of fields are related to the user interface, and we will discuss them in future articles, after viewing the data query application.

In the Campos_Entidades table, there are defined additional fields for the entities which can be added to the downloaded dataset. For example, in PISA database, the countries, along with the name, have a three letters or number ISO code. id_campo is an autoincremental key, idioma (language) is the language of the field name, in the traduccion (translation) field, and id_entidad is a foreign key to the entities table.

identificador (identifier) is the internal identifier to refer to in thee queries, as in entities, finally, with the por_defecto (by default) field, we indicate that this is the default field to return as the entity identifier.

The last table in this group is Filtros_Entidades, in which we define the dependencies between entities in order to filter them. For example, in PISA database all the entities can be filtered by year, as depending on the year, there are different countries, questionnaires, etc.

The last tables we will see in this article are those dedicated to configure options for the queries in the databases.

The Opciones (options) table contains the different options. id_opcion is the autoincremental key of the table, nombre_bd is the foreign key to the database, tipo (type) is used to indicate the type of option and the type of control used to edit the option in the user interface. By the moment, the unique type is 1, which is for a binary option (yes or not, by example), edited with a checkbox control. orden (order) is used to order the option list when are shown to user.

The Valores_opciones (option values) table contains all the possible values for the options, although, by the moment, is only used to indicate default values. In a future, it can contain, by example, a list of possible values to select in a combo box or list box.

The table Traducciones_Opciones (options translations) is used to configure the user interface. The field idioma (language) defines the language of the translation, the traduccion (translation) field is for the option name, and descripcion (summary) contains a longer description of the field and his meaning and use. The id_valor field is used to indicate the default value for the option.

API to query the database

This database is not queried directly, but by means of an API formed by a few stored procs. The calls are made to the procedures of the PODConfiguracion database, ant they compose the calls to the equivalent in the corresponding database in function of the parameters passed.

The parameters used to filter the queries are composed with strings formed by the entity identifier and a list of comma separated key values to include (if they are enclosed in parentheses) or exclude (if they are enclosed in square brackets). To construct these filter strings, there is a specialized class library which we will see along with the query application WinPODUtil.

With the database options the process is similar and they are also passed to the procedures in a parameter in form of string.

To not overextend, this is all by the moment. We will see more over this API and their use in subsequent articles which deal with the class library and implementation examples of data access services.