1

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 (type_id) 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 (entity_type_id) 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?

enter image description here

4
  • I don't see why you need different "type" tables if all of them have the same structure. What's wrong with the first two tables? It's unclear to me what you actually want to store there, but having a "type_id" seems like a reasonable solution – a_horse_with_no_name Sep 30 '20 at 12:09
  • @a_horse_with_no_name sorry for the confusion, I edited the question. a "Type1" table can contain number values, "Type2" table can contains string values, 'Type 3' another data type and so on. – Slim Sep 30 '20 at 13:16
  • Reading this (not lightweight) answer from SO may help start you in the right direction: stackoverflow.com/questions/4304217/… – bbaird Sep 30 '20 at 13:59
  • Without knowing the exact context of what entities your storing, how they're being consumed, etc. I don't think I could provide a satisfactory answer. – bbaird Sep 30 '20 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.