Data types should be changed in gaiadb

[kzollove]

These data types are holdovers from the prototype of the data model. Some of them (varchar(x) in particular) go against some best practices for postgres. In general, they should be more simple unless they provide some benefit of being complex.

Data model can be viewed here

Consider:

• What data type for text fields?
• What data type for PK fields?
• Value_as_number fields?
• Use JSON type for _spec fields?

This shouldnt be a hard problem. This will be a win if the data types used are more uniform.

[kzollove]

@tibbben would love your input on this

[p-talapova]

Hi @kzollove
While these considerations may not be new, they are still relevant.
In the context of OMOP convention with PostgreSQL:

• Text fields are typically defined as varchar(255).
• PKs are usually of the integer (or bigint) data type.
• For value_as_number, the float data type is used.
• When it comes to storing custom extensions to the OMOP CDM model, using the JSON data type for "_spec" fields can be a suitable choice.