To land the exercise, let us think that the User are executives of a bank, which have a client portfolio.
User-Client Relationship
To relate User with Client you need an intermediate table user_client with fields (id, client_id, user_id) .
Each client can only have one executive, and one executive can manage many clients. For the same reason:
In that table the client_id field should be a single key
CREATE UNIQUE INDEX client_id_idx ON user_client USING btree (client_id);
Each record in that table must correspond to a customer of the bank. You can not have a customer record that does not exist, and you must delete the relationship if a customer leaves the bank:
ADD CONSTRAINT client_id_fk FOREIGN KEY (client_id)
REFERENCES client(id) ON DELETE CASCADE;
A client can only have one executive, but he can also not have an executive assigned . For example, if an executive leaves the bank, the clients are left without an executive, but they do not stop being clients of the bank.
ADD CONSTRAINT user_id_fk FOREIGN KEY (user_id)
REFERENCES user(id) ON DELETE SET NULL;
Those clients are waiting to join a new executive.
Relationship User-Attribute and Client-Attribute
You say that the table is Attribute (id, Name, Value). But this does not make sense, it is not third normal form. You are telling us that the table says something like:
id | nombre | valor
-------------------------
1 | pelo | negro
2 | pelo | rubio
3 | pelo | pelirojo
4 | genero | hombre
5 | genero | mujer
That in itself is already rare, but anyway, let's suppose that each Attribute tuple identifies a unique attribute-value combination.
Now let's think that there is a table that relates a person with an attribute (person_attribute) so that you can list somewhere that a client has blond hair or an executive of the bank is a woman.
To maintain relational integrity, the table that bypasses a person and an attribute must refer to an existing person and associate it with an existing attribute. But if that field could be both the id of a User as the id of a Client. In your statement do not say if it is valid that both the User and the Client come from a parent table Person. Otherwise, it is better to use a bridge table for both cases.
Table user_attribute (id, user_id, attribute_id)
Table client_attribute (id, client_id, attribute_id)
If an executive or a client leaves the bank, the relationship disappears, therefore in both cases it happens that the reference to user_id or to client_id entails ON DELETE CASCADE . On the contrary, you should not be able to eliminate attribute 1 because you do not want to lose a client's hair color or leave it bald by saying that your hair is null. Therefore:
ADD CONSTRAINT atributo_id_fk FOREIGN KEY (atributo_id)
REFERENCES atributo(id) ON DELETE RESTRICT;
But here comes the problem of your statement
As the entity Attribute is proposed, a client could have several hairs of different colors. Or several genders, or several ages, etc. It depends a lot on the type of attribute you want to map. For me, the scheme is badly planned, and instead there should be an attribute table with no values (only an id that identifies each possible attribute for a user or client)
id | nombre
---------------
1 | pelo
2 | genero
3 | DNI/RUT/CNI
4 | edad
and two tables that characterize a client and a user:
Table user_attribute (id user_id attribute_id value)
So that it would look something like:
id | user_id | atributo_id | valor
------------------------------------
1 | 1 | 1 | negro
2 | 1 | 2 | mujer
3 | 1 | 3 | 15.192.334-E
4 | 1 | 4 | 45
If this table had a unique key on (user_id, attribute_id) it would be guaranteed that a user does not have two hair colors. The two restrictions above are maintained: the user must exist, the attribute must exist, delete a user deletes the relationship, delete an attribute pulls an error if a user is already associated with that attribute.
The only thing I do not like about this approach is that a user could have red hair and another redhead . One could have gender man and another masculine .
There would not be a list of possible values from which to choose. But that really is getting very elaborate.