Subject: Re: 5 tables with 3 different relation cases
To: pgsql-sql@lists.postgresql.org
References: 
 <CAAY=A7-gS0O_+g_dnnB-NhHOpDEys+WoW=meU38dhuDFuopYiA@mail.gmail.com>
From: Rob Sargent <robjsargent@gmail.com>
Message-ID: <66d7f649-fef5-0df2-f485-7a9eb3ee832e@gmail.com>
Date: Mon, 13 Dec 2021 20:47:16 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.14.0
MIME-Version: 1.0
In-Reply-To: 
 <CAAY=A7-gS0O_+g_dnnB-NhHOpDEys+WoW=meU38dhuDFuopYiA@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------E74673569EF40C0F481AAC8D"
Content-Language: en-CA
Archived-At: 
 <https://www.postgresql.org/message-id/66d7f649-fef5-0df2-f485-7a9eb3ee832e%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------E74673569EF40C0F481AAC8D
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

On 12/13/21 7:32 PM, JORGE MALDONADO wrote:
> Hi,
>
> I have 5 tables related among them so that:
>
> * T1 is parent of T2
> * T2 is parent of T3
> * T3 is parent of T4
> * T4 is parent of T5
>
> image.png
>
> However, in some cases one table is missing so that:
>
> * T1 is parent of T3
> * T3 is parent of T4
> * T4 is parent of T5
> image.png
>
> 3 relations in total.
>
> And yet, in some cases an additional table is missing so that:
>
> * T1 is parent of T4,
> * T4 is parent of T5
>
> image.png
> The 5 tables always have the exact same fields but, of course, the 
> relations are different among each case.
>
> Is there a way to design the schema so that I always have 5 tables and 
> make T2 and T3 "invisible" for cases #1 and #2?
>
> Or should I consider 12 tables: 5 for case #1, 4 for case #2 and 3 for 
> case #3.
>
> I will very much appreciate your feedback.
>
> Respectfully,
> Jorge Maldonado
Have you rejected using a single table with exactly the same columns + 
parent_id?


--------------E74673569EF40C0F481AAC8D
Content-Type: multipart/related;
 boundary="------------DE78F6A2E147C6F328229B22"


--------------DE78F6A2E147C6F328229B22
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 12/13/21 7:32 PM, JORGE MALDONADO
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAAY=A7-gS0O_+g_dnnB-NhHOpDEys+WoW=meU38dhuDFuopYiA@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">Hi,<br>
        <div><br>
        </div>
        <div>I have 5 tables related among them so that:</div>
        <div><br>
        </div>
        <div>* T1 is parent of T2 </div>
        <div>* T2 is parent of T3</div>
        <div>* T3 is parent of T4</div>
        <div>* T4 is parent of T5</div>
        <div><br>
        </div>
        <div><img src="cid:part1.541A4CB9.B0CCFD98@gmail.com"
            alt="image.png" class="" width="542" height="100"><br>
        </div>
        <div><br>
        </div>
        <div>However, in some cases one table is missing so that:</div>
        <div><br>
        </div>
        <div>
          <div>* T1 is parent of T3 </div>
          <div>* T3 is parent of T4<br>
          </div>
          <div>* T4 is parent of T5</div>
          <div><img src="cid:part2.6D8C4C61.9DB4C067@gmail.com"
              alt="image.png" class="" width="542" height="130"><br>
          </div>
          <div><br>
          </div>
        </div>
        <div>3 relations in total.</div>
        <div><br>
        </div>
        <div>And yet, in some cases an additional table is missing so
          that:</div>
        <div><br>
        </div>
        <div>* T1 is parent of T4,</div>
        <div>* T4 is parent of T5<br>
        </div>
        <div><br>
        </div>
        <div><img src="cid:part3.0F298EBC.C7A645D0@gmail.com"
            alt="image.png" class="" width="433" height="126"><br>
        </div>
        <div>The 5 tables always have the exact same fields but, of
          course, the relations are different among each case.</div>
        <div><br>
        </div>
        <div>Is there a way to design the schema so that I always have 5
          tables and make T2 and T3 "invisible" for cases #1 and #2?</div>
        <div><br>
        </div>
        <div>Or should I consider 12 tables: 5 for case #1, 4 for case
          #2 and 3 for case #3.</div>
        <div><br>
        </div>
        <div>I will very much appreciate your feedback.</div>
        <div><br>
        </div>
        <div>Respectfully,</div>
        <div>Jorge Maldonado</div>
      </div>
    </blockquote>
    <font face="Courier New, Courier, monospace">Have you rejected using
      a single table with exactly the same columns + parent_id?<br>
      <br>
    </font>
  </body>
</html>

--------------DE78F6A2E147C6F328229B22
Content-Type: image/png;
 name="image.png"
Content-Transfer-Encoding: base64
Content-ID: <part1.541A4CB9.B0CCFD98@gmail.com>
Content-Disposition: inline;
 filename="image.png"

iVBORw0KGgoAAAANSUhEUgAAAsgAAACECAYAAACNrt1IAAAG9UlEQVR4nO3dQU5TbRuA4ef8
cQF1B4a6BkeQoANgCe0SygRMDB36DZi0Ex3RKY7o0CGdGBMaB66BVpfgDs4/+ELzoHwKPeA5
pdeVMBBo8mIf+t6lb9uiLMsyAACAiIj4X90LAACAJhHIAACQCGQAAEgEMgAAJAIZAAASgQwA
AIlABgCARCADAEAikAEAIBHIAACQCGQAAEgEMgAAJAIZAAASgQwAAIlABgCARCADAEAikAEA
IHn0gTwej6Moit9+7O/v33iZvb29mlZNkzx//vyPMzSfzyMiYm9v79rnzRAATXCXHvp5L7v6
GI/HNf8Uf8+jD+ROpxNlWS4+dnd3o91uX/vcycnJ4vuLoohut1vjimmay8vLxaxcXFxERMTZ
2dm1GdrY2IjhcBgbGxvXvncymYhk7rQxTafTtd6UuNld7qhnV18bDoc1rJomuUsPXV5exu7u
7rWvlWUZnU6n5p/i73n0gXwXVyFTlmW02+2aV8OqOTo6unZna3NzM3q9Xkwmkxs3LtbHbTem
+XweW1tbMZvNFp/v9XrR7XZjOp3W/WNQo9veUc9+fnQUuD2BnJyfn0dZlnUvg0fk2bNndS+B
FXL1CEQOnTdv3kRExJcvX+paFitoOp3GaDSKs7OzupcCK0kgwwP6/v17RMQvf9kBeEjHx8fR
6/XixYsXdS8FVpJAhgcyn89jNBpFr9ereymssJ2dnWi323F0dFT3UlgR4/E4JpPJ4tEHWMZk
Mrl2xn3dzrELZHggV+f/8rlk+JP5fH5tU4r49/wp3Fa3243BYOCRK5aWz7yXZRlnZ2fR7/fX
6ly7QIYHsL+/H5PJZPFkGrit/EooZVnGzs7OWv71huUMh0OPOHDvOp1O9Hq9GI1GdS/lrxHI
cM+Gw+HiyTGbm5t1L4cVd3JyEru7u9Hv9+teCg03n8+j3+/H6elp3UvhEbp60vm6vCqTQIZ7
NB6Po9/vx2AwWKvXi+RhvXr1KiLWZ2NiOV+/fo2IiK2trcURnauXLO33+1EUhZcLZGmfPn2K
dru9Nkd3BDLck+l0Gt1uN3q9noc3uVdeDYXb+Pn1tsuyjNlsFhERg8EgyrL0qBZ/NB6Pfzlr
PBwOYzKZxPHxcU2r+vsEcpLfWnE2m117Bqd73fzJ1Q3HaDT65R2uvJsetzEej3+ZlfF4HKPR
KAaDQU2rAtZJp9P5ZR/r9/sxm83W6pHRJ3Uv4G87Pz9f6msQ8e+74/3Xm8mYH6rqdDpxenq6
ePWKKxcXF/7yB9yr3+1Z3jQtoij9LwAAwIIjFgAAkAhkAABIljqD/PP5OJqtaadozM9qMT9U
YX6owvxQRZX5WfpJek0bWm7W1F9m87MazA9VmB+qMD9UUXV+HLEAAIBEIAMAQCKQAQAgEcgA
AJAIZAAASAQyAAAkAhkAABKBDAAAiUAGAIBEIAMAQCKQAQAgEcgAAJAIZAAASAQyAAAkAhkA
ABKBDAAAiUAGAIBEIAMAQCKQAQAgEcgAAJAIZAAASAQyAAAkAhkAABKBDAAAiUAGAIBEIAMA
QCKQAQAgEcgAAJAIZAAASAQyAAAkAhkAABKBDAAAiUAGAIBEIAMAQCKQeTCnp6d1LwEA7sz+
hUDmwXz48CGKoojXr1/XvRRWkA2KKswPVdi/EMg8uPfv37uh4c5sUFRhfrgP9q/1VZRlWd75
QkURS1yMGhRFUfcSrmm1WvHjxw/zsyLq/F1/+fJlfP78efHvw8PDePfundufFWJ+qML+RRVV
f9cF8iPXlA2q1WrFwcFBHB4extOnT83PirBBUYX5oQr7F1VUnR9HLHhQrVYr3r59G9++fYt/
/vknWq1W3UvijsqyrOVje3t7sYY8R6wW88Oqsn+ttyd1L4DH6+DgILa3t92osLT8lxtzxF2Z
H5Zl/8IRi0euiddVE9fEzeq8rj5+/HjjBmV+Vof5oYomXldNXBM3cwaZ32riddXENXGzJl5X
TVwTN2viddXENXGzJl5XTVwTN3MGGQAA7pFABgCARCADAEAikAEAIBHIAACQCGQAAEgEMgAA
JAIZAAASgQwAAIlABgCARCADAEAikAEAIBHIAACQCGQAAEgEMgAAJAIZAAASgQwAAIlABgCA
RCADAEAikAEAIBHIAACQCGQAAEgEMgAAJAIZAAASgQwAAIlABgCARCADAEAikAEAIBHIAACQ
CGQAAEgEMgAAJAIZAACSJ8tesCiK+1wHa8b8UIX5oQrzQxXmZz0UZVmWdS8CAACawhELAABI
BDIAACQCGQAAEoEMAACJQAYAgEQgAwBAIpABACARyAAAkAhkAABIBDIAACQCGQAAEoEMAACJ
QAYAgEQgAwBAIpABACARyAAAkAhkAABIBDIAACQCGQAAkv8Dho9nOce0Q3EAAAAASUVORK5C
YII=
--------------DE78F6A2E147C6F328229B22
Content-Type: image/png;
 name="image.png"
Content-Transfer-Encoding: base64
Content-ID: <part2.6D8C4C61.9DB4C067@gmail.com>
Content-Disposition: inline;
 filename="image.png"

iVBORw0KGgoAAAANSUhEUgAAAlgAAACQCAYAAADUQhUsAAAGsElEQVR4nO3dMVITbRjA8We/
8QDxBgzxDFYwgxaEIyRHwEaccUxpQ5M0UpEWK7a0NI3jDBkLGz0ARI7gDfYrHPi+CKLAI7tL
fr8ZCpOReXBf3/zd3cSiqqoqAABI80/dAwAA3DcCCwAgmcACAEgmsAAAkgksAIBkAgsAIJnA
AgBIJrAAAJIJLACAZAILACCZwAIASCawAACSCSwAgGQCCwAgmcACAEgmsAAAkgksAIBkAgsA
IJnAAgBIJrAAAJIJLACAZAILACCZwAIASCawAACSLW1gPXr0KIqiuPJrPp9f+ntms1lNUwPA
RWVZ/vY17dmzZxERsbW1denzZVnW/FPcL0sbWMfHx1FVVVRVFUdHRxERcXh4eP5YVVWxuroa
ERHj8TiKooiTk5M6R6ahrrOxzWYzmxqQrt/vL7x+9Xq96Ha7C4/t7+9HxI/Xv16vt/BcVVXR
7/dr/inul6UNrD81m81iOBzGaDSKw8PDusehgf50Y5vP57G+vh4nJyfnj29vb8dgMHBWdMnd
5Ix6RJw/Nx6Pa5gauIrA+o21tbWoqipevXpV9yi03Orq6sKZ0YiIly9fRkTEp0+f6hqLBrjO
GfUzZ2dFgWYSWAAtM5vNYjKZOKsODSawoEabm5vR7XadIeVadnd3Y3t7Ox4/flz3KLTUdDpd
uATtMnO+B3UPAMtkPp9Ht9s9/3W3243j4+MaJ6JtyrKM6XTqTTfc2M97TlmWMRgM4vT09PxG
eG7PGSy4Q2f3YZ19bW5u+tcj1zIYDGI0Gl24Jwtuqt/vx/b2dkwmk7pHuVcEFtRof38/er1e
DIfDukehBcbjsUvK/BUrKysREZe+W5WbEVhQs6dPn0aEjY2rzefzGA6HcXBwUPco3EMfPnyI
brfrzGgigQU1Oz09jYiwsXGlz58/R0TE+vr6+Y3JZ/fzDYdD/8sEf6Qsywsf8TEej2M6ncbu
7m5NU91PAus3/v/J24PBICL+2+C2trZqno42KcvywpopyzImk0mMRqOapqItfv5A26qqzm90
H41GUVVVrK2t1TwlTdfv92MymSy8g3A4HMbJyYlPck/mXYTx34eJXvc5uI5+vx8HBwdRFMXC
40dHR14YgVTv37//5XNe0+6GwIJkV21sVz0HwP1RVFIWACCVe7AAAJIJLACAZGn3YP184y7N
1tQrw9ZRuzRtHVk/7WL9cBtNWz8/S73Jvek/LD80fROxjtqhqevI+mkH64fbaOr6+T+XCAEA
kgksAIBkAgsAIJnAAgBIJrAAAJIJLACAZAILACCZwAIASCawAACSCSwAgGQCCwAgmcACAEgm
sAAAkgksAIBkAgsAIJnAAgBIJrAAAJIJLACAZAILACCZwAIASCawAACSCSwAgGQCCwAgmcAC
AEgmsAAAkgksAIBkAgsAIJnAAgBIJrAAAJIJLACAZAILACCZwAIASCawAACSCSzu1MHBQd0j
AEvOPsRdEFjcqbdv30ZRFPHixYu6RwGWlH2IuyCwqMXe3p4NjhtzBoIM9iH+pqKqqirlGxVF
JH0r/rKiKOoeYUGn04kvX77EysqKddQidR6rJ0+exMePH2NnZyfevHnTiJm4nibuQ9+/f7d+
WqINf9edwVpSVVXV8rWxsXE+Q6fTidevX8e3b99iZWWlxj8N2soZiHZr2j4EmR7UPQDLp9Pp
xPPnz2NnZyc6nU7d43ALTTkLsbe357Ih12If4m9ziXAJ1Xms3r17FxsbG7/c0Kyj9mjCJcKI
xRfKhw8fWj8t0cR9yP7THm04VgJrCTX5WDV5NhbVHVhfv369cAbC+mmPJh6rJs7E5dpwrATW
EmrysWrybCxyBoLbaOKxauJMXK4Nx0pgLaEmH6smz8aiJh6rJs7E5Zp4rJo4E5drw7HyLkIA
gGQCCwAgmcACAEgmsAAAkgksAIBkAgsAIJnAAgBIJrAAAJIJLACAZAILACCZwAIASCawAACS
CSwAgGQCCwAgmcACAEgmsAAAkgksAIBkAgsAIJnAAgBIJrAAAJIJLACAZAILACCZwAIASCaw
AACSCSwAgGQCCwAgmcACAEgmsAAAkgksAIBkAgsAIJnAAgBIJrAAAJI9yPxmRVFkfjuWlHXE
bVg/3Ib1Q5aiqqqq7iEAAO4TlwgBAJIJLACAZAILACCZwAIASCawAACSCSwAgGQCCwAgmcAC
AEgmsAAAkgksAIBkAgsAIJnAAgBIJrAAAJIJLACAZAILACCZwAIASCawAACSCSwAgGQCCwAg
mcACAEgmsAAAkgksAIBkAgsAINm/zZf21K/iszYAAAAASUVORK5CYII=
--------------DE78F6A2E147C6F328229B22
Content-Type: image/png;
 name="image.png"
Content-Transfer-Encoding: base64
Content-ID: <part3.0F298EBC.C7A645D0@gmail.com>
Content-Disposition: inline;
 filename="image.png"

iVBORw0KGgoAAAANSUhEUgAAAbEAAAB+CAYAAACqGS9kAAAE90lEQVR4nO3dv3ISbRTA4bPf
eAFcghO4BqtkJp0pLUlnGxu1kVKLNFBZSRurbGkpjY1UXgOJXoJ38Fp8k4wx+C+A7x54npkU
wpA5yrK/3ZcFm1JKCQBI6L/aAwDAXYkYAGmJGABpiRgAaYkYAGmJGABpiRgAaYkYAGmJGABp
iRgAaYkYAGmJGABpiRgAaYkYAGmJGABpiRgAaYkYQAVt20bTNL/8efLkSUREHB0dLb2/bdvK
f4v6Gv+zM0B9R0dHsVgsYrFY3LpvMBjEYDCI9+/fV5is25yJwR0MBoPfHkVfXl7eetzVfZPJ
pMLUsH12LmJ32flcPWY+n1eamq5ZLBZRSolSSnz8+DEiIs7Pz69vK6XE3t7ejcdcLQ0B67Nz
Efubnc9kMommaeLi4qLmyGyB+Xwe0+k0zs/Pa48CW2XnIvan5vN5jEajGI/Hdjys7PT0NE5O
TuLBgwe1RyGp2Wx2Y8XIkvT/7tUeoKv29/fj6poXVwCxirZtYzabOaPnzn682KNt2zg+Po4v
X77EmzdvKk3VDc7EYMOOj49jPB7feo8M7mo4HMbJyUlMp9Pao1QnYrBBk8kk+v1+vHjxovYo
bJn79+9HRCy9CnaXWE6EDbm8vIzRaHR9ARGs04cPH6Lf7+/8Gb4zMdiQT58+RUTEwcHB9Zvx
/X4/IiJGo5GPbfBH2ra99fGMyWQSs9ksTk9PK03VHSIGGzIcDm98dKOUcn1xx3g8jlJK7O/v
V56SrhsOhzGdTm9cmTgajeLi4iKGw2Ht8aqznAjQAb/6SinfDvhzzsQASMuZ2E/M5/M4ODi4
cdvVnx8+fOiLOLn2/WcKf2dvb89RNayRb7EHIC3LiQCkZTmRqpqmqT0Cf6FrCze2n1w2sf2I
GNV1bcfIcl0Nhu0nh01tPytHrKsbNst5wQPbZC1nYnaMOTjgALaNCzsASEvEAEhLxABIS8QA
SEvEAEhLxABIS8QASEvEAEhLxABIS8QASEvEAEhLxABIS8QASEvEAEhLxABIS8QASEvEAEhL
xABIS8QASEvEAEhLxABIS8QASEvEAEhLxABIS8QASEvEAEhLxABIS8QASEvEAEhLxABIS8QA
SEvEgM47OzurPQIdJWJA5719+zaaponnz5/XHoWOETEgjdevX4sZNzSllLLSL2iaWPFX8I/U
fK7Ozs7i8ePHt263/eTRNE3tEW7o9Xrx9etX208Sm3qtOxPjn7ActB1KKVV+Dg8Pr2fo9Xrx
8uXL+Pz5c8V/CbpCxPinLAdxV9/H69WrV9Hr9WqPRAdYTtwhloNYRc3X+rt37+Lw8PBWuOx/
8rCcyFpYDiKjR48eOfNiqXu1B2B39Hq9ePr0aTx79swOCVgLy4k7xHIQq+jic9XFmVhuU8+V
iO2QLj5XXZyJ5br4XHVxJpbznhgA/EDEAEhLxABIS8QASEvEAEhLxABIS8QASEvEAEhLxABI
S8QASEvEAEhLxABIS8QASEvEAEhLxABIS8QASEvEAEhLxABIS8QASEvEAEhLxABIS8QASEvE
AEhLxABIS8QASEvEAEhLxABIS8QASEvEAEhLxABIS8QASOte7QGgaZraI5CY7We3iRhVlVJq
j0Bith/WEjFHQgDUsHLEHAkBUIsLOwBIS8QASEvEAEhLxABIS8QASEvEAEhLxABIS8QASEvE
AEhLxABIS8QASOsbdesriVcAaIAAAAAASUVORK5CYII=
--------------DE78F6A2E147C6F328229B22--

--------------E74673569EF40C0F481AAC8D--