Sql-server – Stable triangle relationship

database-designsql serversql-server-2012

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.

Best Answer

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

Related Question