# Stable triangle relationship?

I have a table that represents a specific type of action. Let's call it `[ACTION]`.

An action can be performed by a user (`[USER]`), so I have the relation `[USER] 1:N [ACTION]`.

The user can have children (`[CHILD]`), so `[USER] 1:N [CHILD]`.

The problem starts by the fact the action must be performed by the user, however it can be performed for the user or one of its children.

If every user always had one or more child, I could simply do `[CHILD] 1:N [ACTION]` instead of `[USER] 1:N [ACTION]`, because over the child I will find the user who performed the action.

If I simply add `[CHILD] 0...1:N [ACTION]`, so the child becomes an optional field in the action table, it's possible a child is added that does not belong to the user that is added on the action.

### Questions

1. What is this scenario called, typically?

2. How to overcome this problem or what is the best way I can go?

I would not like to check this in code, a database-only (SQL Server 2012) solution would be perfect.

### Example

The action is an investment. The user will always take the investment, but he/she can decide he/she is investing the money for him/her or for one of his/her children. In this scenario children are underage, always. If a child is old enough to do investments of its own, it will register and become a user, instead. So this is why a child cannot be a user. They need to stay two different entities.

So I kinda need two relations that stay stable to each other. I need to know who (user) took the action (investment) and I need to know for who (user or one of his/her children) the action (investment) is taken.

What I'm seeking is a solution where an action (investment) cannot be made for a child that 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.

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`).

• Design 2 is not acceptable to me, since this is a part of a larger environment, I need to separate children from users in general. Design 1 looks interesting. I know very well I can reference another table with two keys at the same time. However, I still lack of understanding: What prevents me from adding a child of a different user in the action table? I still could reference user A in action while the child in action references user B, I could not? May 13, 2016 at 10:32
• No you couldn't. The `FOREIGN KEY (user_id, child_id) REFERENCES child (user_id, child_id)` ensures that the `action (user_id)` is the same as your child's `user_id`. May 13, 2016 at 10:38
• About the "unacceptable" design 2 (or 2b): The internal design and structure of tables does not have to be visible to the users of the system/application. If the users only see the tables/views `user / action / child`, then - as far as they know - users and children are separated. May 13, 2016 at 11:05
• Finally had time to implement your design 1. Works like charm. Thank you! May 16, 2016 at 22:45

If this is about real people then child may became a user some time and may have his/her children. Also a child may have two parent. So i'd better have a PERSON instead of USER, a CHILD_PARENT and ACTION this way (pseudocode). The only but essential difference from ypercube's answer is CHILD_PARENT structure. I also feel that proper naming greatly helps system development. For example User and Child are not entities but roles of PERSON in ACTION or CHILD_PARENT,

``````CREATE TABLE person
( person_id INT NOT NULL PRIMARY KEY
) ;

CREATE TABLE child_parent
( parent_id INT NOT NULL,
child_id INT NOT NULL ,
FOREIGN KEY (parent_id)
REFERENCES person (person_id),
FOREIGN KEY (child_id)
REFERENCES person (person_id),
CONSTRAINT (child_id <> parent_id), -- prevent short circuit
PRIMARY KEY (parent_id, child_id)
) ;

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 person (person_id),
FOREIGN KEY (user_id, child_id)
REFERENCES child_parent (parent_id, child_id)
) ;
``````
• A child is always underage and will never become a user without getting over 20 and taking action and registering by itself as user again. Only father or mother is responsible for investments of their children. We don't set priority on the case mother and father registers and want to invest for the same child, but we should take this in account, indeed. Investing for a child is optional, usualy users invest for themselves. (I updated my question to clarify this) May 13, 2016 at 10:39
• Not taking wider context into accout, this just means that when creating new ACTION, a clerk should check all prerequsites (age, status, ...) concerning a person which would be User. Considering wider system context, it's quite reasonable to have a separate USER entity storing common info about PERSON's qualification as a User in different ACTIONs.
– Serg
May 13, 2016 at 10:46