Repeated id in children tables

Question

I'm wondering what the pros and cons of having a repeated id in child tables in a database.

For example, consider a table parent:

create table parent (
  id int,
  a text,
  b text
)

And this parent has a child1 table that references it:

create table child1 (
  id int,
  parent_id int not null references parent(id),
  c text,
  d text
)

All fine and good, and nothing out of the ordinary. The problem comes in as you keep drilling down:

create table child2 (
  id int,
  child1_id int not null references child1(id),
  e text,
  f text
)

create table child3 (
  id int,
  child2_id int not null references child2(id),
  g text,
  h text
)

The issue I'm having is, the further you get down, the more tedious it becomes to join your way upwards. One solution I've considered is to repeat the parent id in all the children tables:

create table child2 (
  id int,
  parent_id int not null references parent(id),
  child1_id int not null references child1(id),
  e text,
  f text
)

create table child3 (
  id int,
  parent_id int not null references parent(id),
  child2_id int not null references child2(id),
  g text,
  h text
)

This helps reduce the number of joins, but it also affects database integrity. You need to always remember to update all the parent_id columns if you switch the parent of child1. My question is: are there any other approaches to handle this situation? If not, is there any way to repeat an id in children tables while still retaining data integrity?

Kuberchaun · Answer 1 · 2014-03-26T00:15:19.190

I'm assuming from your example it's important that the relation goes parent<-child1<-child2 and not parent<-child1 and parent<-child2.

So how about something like this.

create table parent 
(
  id int,
  a text,
  b text
);

create table child 
(
  id int,
  parent_id int null references parent(id),
  previous_child_id int null references child(id)
  c text,
  d text
);

A second approach that is more normalized would be this. This assumes that the id on parent_child can be assumed to be an increasing integer so you can always figure out order of the children.

create table parent 
(
  id int PRIMARY KEY,
  a text,
  b text
);

create table child 
(
  id int PRIMARY KEY,
  parent_id int null references parent(id),
  previous_child_id int null references child(id)
  c text,
  d text
);

create table parent_child
(
  id serial PRIMARY KEY,
  parent_id int null references parent(id),
  child_id int null references child(id)
);

score 0 · Answer 2 · answered Mar 25 '14 at 23:59

Solution by @JustKim is OK, but you can go even further

create table person
(
  id int,
  parent_id int null references person(id),
  c text,
  d text
);

If parent_id is null, that record is parent.

Of course, to get all children of one parent, you have to use recursion in your code.

Repeated id in children tables

2 Answers2