There are 2 types of users and each has about 11 types of feedback forms (each type has its own fields), each has 15-30 fields and many fields in which are the same (from 50% to 70%). How best to design it? and accordingly, how then is it better to remove the data, for example, if you want to display all forms filled out by a specific user?

  • 2
    Sparse table (in fields that are not in the form - Null). - Akina
  • What other options might there be? and in what case would it be worth using separate tables for different forms? - Nikita
  • one
    What other options might there be? Eav. Serialization (XML, JSON). in which case it would be worthwhile to use separate tables for different forms Yes, almost in no way The appearance of a new form will require changing the structure of the database from the client code, which is generally not encouraged. - Akina
  • Thank! Perhaps I will go to read the theory :) - Nikita

1 answer 1

You can use a database with support for table inheritance. I use postgresql for this.

Suppose there is an abstract entity - user_form. In this entity, you can describe all the fields that will be common to all entities.

CREATE TABLE user_form ( id serial NOT NULL, type character varying NOT NULL, field1 character varying, --..... CONSTRAINT user_form_pkey PRIMARY KEY (id), );

Further entity user_admin_form

 CREATE TABLE user_admin_form ( -- Унаследована from table user_form: id integer NOT NULL DEFAULT nextval('user_form_id_seq'::regclass), -- Унаследована from table user_form: type character varying NOT NULL, -- Унаследована from table user_form: field1 character varying, field2 character varying, CONSTRAINT user_admin_form_pkey PRIMARY KEY (id), ) INHERITS (user_form);

By analogy, you can create other child tables. Be sure to use the record type pointer to determine which table it is in, because all the records will be available in the parent table.