I see 2 options to implement this. The first would be what you already have, with a minor adjustment, to enforce this part:
What I'm seeking is a solution where an action (investment) cannot be made for a child the user has no relationship to. In other words, the father/mother can only invest for him/her or his/her child, not for the child of someone else.
The adjustment would be to have a composite key on the action references (child) foreign key. The action (child_id) is nullable but when the value is not null, the foreign key constraint ensures that it references a child of the user that takes the action.
Sample code:
-- design 1
CREATE TABLE user
( user_id INT NOT NULL PRIMARY KEY
) ;
CREATE TABLE child
( user_id INT NOT NULL,
child_id INT NOT NULL PRIMARY KEY,
FOREIGN KEY (user_id)
REFERENCES user (user_id),
UNIQUE (user_id, child_id) -- this is needed for the FK below
) ;
CREATE TABLE action
( action_id INT NOT NULL PRIMARY KEY,
user_id INT NOT NULL,
child_id INT NULL, -- nullable
FOREIGN KEY (user_id)
REFERENCES user (user_id),
FOREIGN KEY (user_id, child_id)
REFERENCES child (user_id, child_id)
) ;
Another way would be to rename the child table to dependent / investor / beneficiary (pick a more appropriate name) and store there not only the children but the users themselves as well (so all investors / beneficiaries / dependent persons of a user). This way only one, the foreign key from action to investor will be needed and the column will be not nullable:
-- design 2
CREATE TABLE user
( user_id INT NOT NULL PRIMARY KEY
) ;
CREATE TABLE investor
( user_id INT NOT NULL,
investor_id INT NOT NULL PRIMARY KEY,
FOREIGN KEY (user_id)
REFERENCES user (user_id),
UNIQUE (user_id, child_id)
) ;
CREATE TABLE action
( action_id INT NOT NULL PRIMARY KEY,
user_id INT NOT NULL,
investor_id INT NOT NULL,
FOREIGN KEY (user_id, investor_id)
REFERENCES investor (user_id, investor_id)
) ;
What you have as table child in the first design, can be a view:
CREATE VIEW child AS
SELECT user_id,
investor_id AS child_id
FROM investor
WHERE user_id <> investor_id ;
As a side effect, with design 2, we don't really need the user_id in the action table (unless for other, not mentioned in the question, or performance reasons). We could remove it and get rid of the composite foreign key as well. The user_id can be found with a join to investor:
-- design 2b
CREATE TABLE user
-- unchanged
CREATE TABLE investor
( user_id INT NOT NULL,
investor_id INT NOT NULL PRIMARY KEY,
FOREIGN KEY (user_id)
REFERENCES user (user_id)
) ;
CREATE TABLE action
( action_id INT NOT NULL PRIMARY KEY,
investor_id INT NOT NULL,
FOREIGN KEY (investor_id)
REFERENCES investor (investor_id)
) ;
CREATE VIEW child AS
-- unchanged
Another way that is more complicated but takes good points from both previous designs and captures all the different entities (persons, users, children, actions) is to use the supertype/subtype pattern.
This essentially adds the following into the design (entity person):
A person is either a user or a child.
(superype with subtypes)
A person can have any number of children.
A child has exactly one parent (user).
(1:n relationship)
A person can be a beneficiary of any number of action (investments).
An action is taken for exactly one beneficiary (person).
(1:n relationship)
Code:
-- design 3
CREATE TABLE person
( person_id INT NOT NULL PRIMARY KEY
) ;
CREATE TABLE user
( user_id INT NOT NULL PRIMARY KEY,
FOREIGN KEY (user_id)
REFERENCES person (person_id),
) ;
CREATE TABLE child
( user_id INT NOT NULL,
child_id INT NOT NULL PRIMARY KEY,
FOREIGN KEY (child_id)
REFERENCES person (person_id),
FOREIGN KEY (user_id)
REFERENCES user (user_id)
) ;
CREATE TABLE action
( action_id INT NOT NULL PRIMARY KEY,
beneficiary_id INT NOT NULL,
FOREIGN KEY (beneficiary_id)
REFERENCES person (person_id)
) ;
We don't need the user_id in the action table, since the user of an action can be found by looking in the other tables, it will either be the beneficiary himself (if the beneficiary_id is in the user table) or it will be the user (from user_id in the related the child).
