What is the best way to redesign the base?

Question

Database design

There are two types of clients in the system: Client Individuals (individuals) and Client Legals (legal entities)
The previous developer implemented the division into types as follows: There is a clients
table that contains information about the manager associated with the client and other business data There is a table client_individuals , which stores a connection with a record from the clients table (client_id) and a set of fields specific to physical. persons (date of birth, passport data, etc.) There is a table client_legals , which stores a connection with a record from the clients table ( client_id ) and a set of fields specific to legal entities. persons (OGRN, website, date of re-registration, etc.)


This option does not seem to me the most convenient
. I have an idea for such a structure: clients
table - stores created_at , updated_at , type (physical/legal) client_fields table - stores character field code ( code ), field name ( name ), field description ( description ) client_values ​​table - stores the field value ( value ), connection with the field character code ( client_field_code ) and the client ( client_id ) But it seems to me that such a thing is a little redundant due to the fact that the field set rarely changes


I'm sure you can come up with a less plump and more comfortable variant

Answer 1

It seems to me that the existing option is quite good and convenient. Why didn't he please you?

Answer 2

The previous developer did everything right, according to the classics .
The proposed alternative is not more convenient from the point of view of constructing relational queries, rather, on the contrary, it is much more inconvenient and slow in the future.
Leave it as it was.

Answer 3

3NF
Everything is ok

Answer 4

clients table - id, created_at, updated_at, type client_fields
table - client_id, field_name, field_value But as I understand it, you have about the same thing, just the description of the fields is placed in a separate table. Quite a decent solution.

Answer 5

In principle, there are still IP clients ...
Moreover, in half of the cases, the attributes of an IP are similar to a physicist, in the other half - to a lawyer ...