5

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.

5

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? – modiX May 13 '16 at 10:32
  • 4
    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. – ypercubeᵀᴹ May 13 '16 at 10:38
  • 2
    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. – ypercubeᵀᴹ May 13 '16 at 11:05
  • Finally had time to implement your design 1. Works like charm. Thank you! – modiX May 16 '16 at 22:45
1

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) – modiX May 13 '16 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 '16 at 10:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.