Information handle on databases

This project was done by Universidad Adolfo Ibañez - Territorial Intelligence Center - mainly by Felipe Matas

Most of the application of this project can be automated using: https://github.com/CIT-UAI/build_raw/

This project starts with a simple issue, when we have surveys, information we usually need them to save on a database, DBs are very useful, are easy to query, easy to write, easy to handle a medium scale of data, they are not all powerfull but very useful.

This is in 2 big sections, each subtitle starts with a concept of information and meaning, then if is implemented it shows the tech part, the 3 big concepts here are:

• State
• Lookup
• References

Now one easy question, if we don't know the value of a cell, how do you express it? we could say NA values, but this start getting complex when we have several states, like if you have a databases of animals and you ask "which is the color of the legs?" (assumption of all them be of the same color) we would have at least 3 possible states, know the color, do not the color, and the question it self does not make any sense! for example that animal do not any leg, which implies the answer does not exists.

For any information we want to get, we always must "ask" to "someone", and before get our ideal answer which is get the value, there is a lot of information on the answer when we do not get the value.

A concrete example, you have a survey and you ask the gender, you can answer male, female or refuse to answer, while is ideal to know the gender, the last scenario tell us about privacy, the will of ppl to answer this question, that is also information on the question, the answer, is not the only valueable information when we ask.

From that point, they core of this project is try to set a experimental base about how to sort this, how to properly codificate questions and answer to represent the information and save it, without loss information on the process.

The basic idea is, instead use NA or any app type, we will represent each value in two columns, in one we will represent the state of the information, and in other one the possible value.

So could express things like Illegible/NA (because we don't know the value), or state "Value" in one column and in other "some info".

This project give us the base to interact with postgres and the struct State.

This project is not made as a full backend to interact with postgres!! We have all the needed operations to handle data with postgres, be able to read a table that has not been serialized correctly, make new table and upload a good final table.

The only piece that was not developed, is be able to read a final version of the table, you will get this better after read.

States

In the process of known an answer, we will the actual states we have to use, they are not perfect, but can mainly help to a lot of places, we will start from the unknown state, and go step by step until when we know the value:

Unconstructed/NullUnconstructed

This state is designed when you don't even know what is being asked, and what the answer can be.

This state one several advantages more than represent the state, is be able to structure information in slow way.

Know what is being written, the format of the answers, get more information to make it right, all process to get information have a cost, even try to read complex information, we do not always need to know the structure right now, not even how to query it, but we need to know we have that info, and that we can organize it later.

Censored

The first step to retrieve most shared information, is know if the source is willing to share it, most of the data is not directly shared, a institution/enterprise make a survey and they share the info, but this does not means they will share everything, some times they can not share some info of several rows due to several reasons, this state represent that

NoExists

This value is when the answer to a question should not exists.

How much heads have a apple? When was your last period? (ask to a men)

Anonymized

In case the value exists, the source can still censor this value, this is different from the Censored state, because we at least know the value it self exists.

Refused

This value is cases when you have a survey, and who you ask is not willing to give you an answer, so you know there is no will for it.

Remember there is usually 2 parts, we ask to who store and handle the info, and to who know the value, in this case the person.

Illegible/IllegibleValue

If all the parts want to share the info, could happen the information went damaged, information corruption, someone wrote very bad at a survey, etc, etc.

In this state we know all the parts have the will to share.

IllegibleValue will share what does it have, the correupted data, while Illegible will just tell the information is corrupted.

NotIn, In, Value

In this scenario all the parts have the willing to share the info and is not corrupted, we have 3 possible cases for this:

• Value: This state just says we have the value and store it
• In: The value is any value on the vector, but it most be only one and is there, you could need more variations for this

NotIn

I want to share some thoughts about this category, in databases, excel, column/rows logic, there is several fundamental rules to keep information health, one of them is "in each column keep always the same type of information".

Imagine you are asking "In which state do you live?" and the options we get some times are "State1", "State2", "State3"... "Other".

Is "Other" a state?

"Other" is not a state, it says that ppl do not live in any of the other actual values, we should make a record that says in which states that ppl do not lives, a vector, other table, etc.

"Other" how is not a state, breaks the health of the column and information we should never use it.

Also is very breakable, imagine you have the value "Other", then you update the data later and you add "State23", now you don't know, but that ppl lives in that state, "Other" also only makes sense when the data is read-only, so we need to keep this records in other place-

In rust it can be used with Option, the reason is when we want to structure information, we need to first read all the options on the table to know in which states it does not live, in this process which is row by row we do not have much ways to build this list, in the meanwhile we can use None, when we process the data we can build the list of places where that ppl do not lives and update the value for example, with a new table name that uses the ppl id.

NoCodable, Value out of codification

Here we actually are able to retrieve the value, or at least how is going to share us the information, this scenario happens when the codification method is able to represent the actual value.

This might be very similar to NotIn cases, after all implies the actual value is other, but not in the current table, the main difference is the codification system, is that you should only be here if you have a static codification system.

Lets pick two examples, you have work categories:

• Adminstrative
• Scientific
• Manual work

We know not all the professions are there, if we are recording the values as String, one might found "No codificable value" as a option, but in this case the right value is "Other", because the strings do not represent a static codification, in any moment someone might include a new string with a new work type, while there is no constrain in what we can write the right state will be NotIn, so why someone mught use "No codificable value"? because not all ppl hit so hard in clarify all this concepts, so be flexible in what others can write.

If we have the previous categories, but we know they belong to the Standard XYZ which specify only that works categories, here the rules change, no one should include something out of the standard, the value must follow only that standard, here is when right state is NoCodable, this case!

If you are structuring data, and want to use static codifications like this one, might be good idea keep the original value in other column too, because the issue with codifications is that you will loose information, in this case with work categories, we loose the original work that is being done, and all ppl who performs out of the category will be hardly be taken in consideration, after all we will know almost nothing from that record, than is NoCodable.

States 2

As you may notice, there is a lot of states! and some of them seems redundant, this is because we have simplified a very usual scenario.

A very usual scenario when someone publish data is:

You -> Enterprise/Institucion who has data -> Entity who answered the values of the data (like ppl in a survey)

In order to reach our information we need to ask first to who shares, and then handle the information of the final entity.

So, if you want more clarity for this, you can always simplify this states using two ones State<State<T>>, the first state will represent our relation with who has the data, the second one the information of the entity.

While this will add a extra complexity layer, because for one State most ppl will intuitively get how to interpret this if we store the state in one column and possible values in others, set a limitation, because this design would be locked to this scenario, and not all data need or can be expressed with that model, so is good to think use a clear and more pure State logic, only the communication of two parts, then handle for our usual scenario.

A useful feature that could be added is merge States, like State<State<T>> will be recorded in two columns, this feature has not been developed but would be very useful to handle this scenarios and improve readibility on databases.

Is good to think which information is the real one, for example make a query to a server, is also a talking process, you could put inside the states even the HTTP protocol, so the questions are, which informatinos are the one we want to query? if we are asking information of a survey, is important if the we are without internet and failed the API? right is not important! it does not add nor have any information of that issue, in most cases we could skip the talk process when is technical, but is good to keep an eye to them.

Happens some servers can query a max information, so if you ask a question which leads to a big answer, it can fails, this tell us the answer is big. this is related to the information we want, worth keep the track, usually the best way to sort this is split query the info, from interpret the info, so you can keep the state of both and all will be organized.

Implementations

There is actually 2 version of this implementation, or lets call them iterations, the second one is the one you would like to use, but there is some scenarios where you may want to take in consideration if you are able to do not break the interpretation of table columns.

This section will not talk about the direct implementation in rust, is more the conceptually and how we will sort the implementations with Postgres, for the docs of how works the implementations, how to write rust code go to:

cargo doc --open

• Module PG1: First implementation on Postgres
• Module PG2: Second implementation on Postgres

If you are reading this from the rust docs, scroll down to the end of the page to find them!

I strongly suggest start reading the rust docs of both implementation before continue with this two sections The next sections are more understable after read them

First implementation

One key feature to keep all ths working, is understand that information stored in one cell, is mostly several information than a single one, if we just pick the current basic example of above, just know the "Name" of someone, already implies two information, the state of the information, and the value it self.

So, the first try to organize this, is use a prefix and enumerate everything:

enum State<T> {
        Unknown,
        Unconstructed,
        Value(T)
}

This would be recorded to a table:

use info::State;

struct Table {
        column1: State<f64>,
}

And recorded in a table using the next columns:

• column1_1: State of the column
• column1_2: Value of the column

There is so much complex examples, the next one could address, "Chile, RM, Santiago, Street N1, 2500".

An address is not "one information", if we talk only about that one, an address is composed by several parts, which should be each one recorded in a different column, so each one would be at least:

• Country
• State
• Substate
• Street name
• Street number

Now you want to record everything on rust, the appropiate way would be make a struct:

use info::State;

struct Address {
        country: State<String>,
        state: State<String>,
        substate: State<String>,
        street_name: State<String>,
        street_number: State<u64>
}

/// Adding it to our table:
struct Table {
        column1: State<f64>,
        column2: State<Address>,
}

This would cause to us have the next table:

• column1_1: State of column1 value

• column1_2: Value if exists of column1

• column2_1: State of the address

• column2_2: State of the country

• column2_3: Value of the country

• column2_4: State of the state

• column2_5: Value of the state

• column2_6: State of the substate

• column2_7: Value of the substate

• column2_8: State of the street name

• column2_9: Value of the street name

• column2_10: State of the street number

• column2_11: Value of the street number

All the code is designed to be already possible to record all on postgres tables, but as you can see, one column which we could think to have a very simple information, can have a lot of it, the enumeration will grow iterating inside the struct/enums and reach each possible value or state and give to them a unique table name.

But this design has its flaws, the main one is the naming, while is ok to have everithing techncally in its right column, is not like we can know what each column means, there is more complex informations that can really mess up the meanings, so, why did I wrote the first prototype like this while knowing this beforehand? Because our instutition is a huge fan of SHP files for tables, I'm not a fan of them but I need to adapt, and SHP format has a limit of 10 characters per column name, so even if I wanted it different I choose this as a first try.

This proof of concept is not only designed to just see it, is also functional, there is already all the needed tools to do this on this project, the design is simple:

First we will read all the info from PG into a struct:

use info::State;

struct Address {
        country: State<String>,
        state: State<String>,
        substate: State<String>,
        street_name: State<String>,
        street_number: State<u64>
}

struct Raw{
        column1: State<f64>,
        address: State<Address>,
}

Now, we really would like to choose better names, and always follow the KISS rule, we do not want to nest data unless is really necessary, so the final struct would look like:

use info::State;

struct Pure {
        column1: State<f64>,
        address_country: State<String>,
        address_state: State<String>,
        address_substate: State<String>,
        address_street_name: State<String>,
        address_street_number: State<u64>
}

Now when this struct is translated to a table we will have:

• column1_1: State of column1
• column1_2: Value of column1 if appropiate
• address_country_1: State of address_country
• address_country_2: Value of address_country if appropiate
• address_state_1: State of address_state
• address_state_2: Value of address_state if appropiate
• address_substate_1: State of address_substate
• address_substate_2: Value of address_substate if appropiate
• address_street_name_1: State of address_name
• address_street_name_2: Value of address_name if appropiate
• address_street_number_1: State of address_street_number
• address_street_number_2: Value of address_street_number if appropiate

As we can see, even having some long names (which are larger than 10chars but lest ignore it for now), all is a lot more radeable.

Second Implementation

This implementation is similar to the first one, but we have decided to get rid of the automatic numering thing, and each enum and struct will be forced to choose a name for each data stored, then we will nest each case based on their names.

Obvs each enum need its own way to be represented on a table, there is traits to perform this.

The simpler case of how to use this is:

use info::pg2::{GetColData2, GetNames2};
use info::pg1::AsPgValue;

#[derive(GetColData2, GetNames2, AsPgValue)]
enum State<T> {
        Value(#[serde(rename = "value")] T),
        Unknown,
}

We have choosed this model because one enum could store several values, so we need to declare there how to represent each one.

I'm abusing a little about serde here, this crate has nothing to do with serde! but if someone may way want to serialize/deserialize with it, I thought would be good use the same attribute as serde, because this case, and serde's case uses it for the same propuse.

Logics on information

As you may notice on this project, handle information is not trivial, transform data, or what we think as information, to structured information is not easy, even the process to know what information is contained in what we think is information can take time and effort.

This project intends to recognize the information, how we structure it, how we use it, how store it, how make it sense and do not lost it!

While we have talked about the information of the questions to retrieve information, there is two pieces we did not touched technically on this project.

Lookup

First think of an easy example, you have a table with houses, you have info about each of them, n° ppl, rooms, now we pick one.

House N° 15, has 3 ppl living there

So, we know House N° 15 has 3 ppl living there, nothing new, nothing weird at all. Let change a little how to view this, rn you have the data "3 ppl living there", this one is a cell on a table, right the full string. How do you retrieve this data? If want to retrieve this cell you need know it belongs to "House N° 15", this is also a path, a key to reach a value, and this is a very special path, because it allow us to retrieve a unique value.

Lets split this a little more, in a more real table, we would save the value "3" in a cell, how do you access this value? We need to know "House N°15", we need the column name "ppl", then we can finally retrieve this unique cell "3". Now lets move to a real table, we know the table is a list of "Houses", we need to look at the id "15", column "ppl" to reach the single value "3".

Oks, too trivial, right? have you noticed? there is some rules to be able to logically reach the value "3":

• We need the "path", which is usually serialized in some way to get it, the path is composed by parts
• As a single value, this means, all "paths" for each value, is a unique combination of keys that allow us to always retrieve this value

The Path is composed by keys, this keys are disgned to be unique as a group, the way to access/retrieve the value, this logic belongs to Sets.

How does this impact here? Each element we want to describe, even its propoerties, must always respect this concept, there must be a set of key that is unique to always retrieve them, this what sets, define, distinguish elements in the infraestructure, this metadata is what sort and allow us to reach and find anything.

You read right, this concept is core on metadata, from the perspective of "3" in the example above, "house", "15" (id), "ppl" (column) are metadata, is the needed information to understand, interpret it!

You may notice something, some tables have things like this "house", "15" (id), "door width", "1.5", while there is enough keys to reach the value in a database, there is not enough keys to reach the information, there is a information loss, have you noticed where?

The first, because there is several information loss, is the units! "1.5", m? feet? inches? the answer, 1 = 40inches, I needed to perform some calcs and this is the measure it comes originally, not intuitive at all! even if would come in a usual unit you wouldn't know it, without this, you can reach the data, but not the information.

The other ones also follow this rules:

• "house": which houses? I'm very sure you are problably not recording all of the world, nor random one, this table is very very probable to be a subset, a conditioned subset of houses that you are recording, if you intend to keep that logic, you should record somewhere this criterios, they are key to interpret
• "door width": All doors of the house? probably one, record which one! in this case was the main door

So, in order to keep our very very valuable information, we need the path to reach each information, and not only the data, going back to more simple use cases, if we have a table:

• area_1
• area_2

How each column represent something different, you need to make keys that allow you to interpret and reach each column, each column will represent a full set of data.

The table name, which will be its path? how do we reach that meaning? which descriptions, which keys.

Expansion and contraction of the concepts

This is more a example, side note, which each information has its path, we can also use this to represent bigger sets of data, just removing keys, while in the exaple above we have:

"house", "15"(id), "people" -> "3"

If we remove any of they keys we can retrieve a bigger set of ppl, if we remove house we can we will get anything that has id 15 and the value of ppl, is meaningless for a lot of uses, but a more usual one is remove the id, ask for all set of ppl that exists in houses is something we could use, this also sets the base for vectors.

There is no rule about how to save the metadata, how to serialize, how to store everything here, even the path can have no order, and can every type of keys we may need, we can adjust to each use case, the important value is recognize what is our information, and how to preserve it.

The source of all this topic, is a way to represent our most escential scenario:

This cell belongs to the house 15 This cell stores a m2 value

Remember all we wanted to do, is a way to organize, and be able to work with this relations, which originally was in a more natual format.

Reference

We have talked until here about, the state of information, how to reach it, the intermediates of data <-> information (keep flaxible the meaning), there is one last piece of ### that is very imporant to us, and is the source, the reference, where our data and information comes!

While the lookup belongs to the metadata and infraestructure place, the actual implementation of states is State<T>, where T is what we want to save, but a complete one would be State<T, W>, where T is what we want to save, and W is how we will save the reference/source of that information.

If we download a table from... NASA, we can try to keep is from NASA, was published 2022, but the info is from 2020, this table is constrained to images only from Texas. All this references are true for each cell of the table.

You may think, so you want me to store all that per cell? isn't that too much?

Well, depende, if the full table has that source, have out of the table organizes thing, and uses less space, basically we are "compressing", but is only valid while all the data has that source.

If you now have data from the NASA, same type of data, images, from Texas but this time they were taken in 2022, is different information, you can't just mix both sources, if you want to keep the integrity of the data, you need to say "This images are from 2020, and this others are from 2022".

While we increases the data, someone of can be only from a part of Texas, other images from other parts of the world, some of them could be only have different publish data, which can imply change on the proces to be published, which leads to differences on data.

When we reach this level of data, have a lot of cense work with the minimum units, table cells, raster tiles, anything that make us easy the access but keeps right the integrity of the information.