postgresql – How to store values of different entity types in database?


I created the schema of ref_entity and ref_entity_type:

create table ref_entity {
id serial primary key,
name varchar(255) NOT NULL,
type_id integer,
code varchar(255)

constraint FK_REF_ENTITY_TYPE FOREIGN KEY (ref_entity_type) REFERENCES REF_ENTITY_TYPE (ID)
}
create table ref_entity_type {
id serial primary key,
name varchar(255) NOT NULL,
}

In the project, I have a batch job which receives many files every day and I need to integrate them into the final tables after some transformations.

The extracted data does not correspond to the same entity_type. There are many type of entities and each type has a corresponding values.

I’m looking for a suitable solution to extend my schema and integrate the values of the different entity type to the entity in order to return them whenever I select a given entity.

Solution:

Create a table for each type of entity :

create table type_x {
id serial primary key,
value xxxx NOT NULL,
entity_type_id integer,

constraint FK_TYPE_X_ENTITY_TYPE FOREIGN KEY (ref_entity_type) REFERENCES REF_ENTITY_TYPE (ID)
}

the caveat of this solution is I need to create as many tables as entity_type and I think it’s not good.
I thought to create a table contains all the values of a given type in an array, however the type is not always the same (could be number, varchar, text, etc…).

Does anyone has any suggestions to better design the schema table and optimize it in order to not create as many tables as types to store the values?

SOLUTION 1