Re: 5 tables with 3 different relation cases

From: Steve Midgley <science(at)misuse(dot)org>
To: Rob Sargent <robjsargent(at)gmail(dot)com>
Cc: pgsql-sql <pgsql-sql(at)lists(dot)postgresql(dot)org>
Subject: Re: 5 tables with 3 different relation cases
Date: 2021-12-14 04:19:09
Message-ID: CAJexoSLmREFskjOMGwvG6ghJb6CoywXr4kOfbrp0Mv75OFWguA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

On Mon, Dec 13, 2021 at 7:47 PM Rob Sargent <robjsargent(at)gmail(dot)com> wrote:

> On 12/13/21 7:32 PM, JORGE MALDONADO wrote:
>
> 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.
>
> Have you rejected using a single table with exactly the same columns +
> parent_id?
>
> I'd second that suggestion - if I had this design problem, I would use the
design pattern "single table inheritance" and just link data on a parent ID
via a self-join. Then it doesn't matter which configurations the data are
in - if you use recursive queries, you can grab all the data you want from
any point in the chain and proceed upward or downward from there to get all
the parent data or child data, as you like. If you need to go upward and
downward in your queries (finding parents from children or children from
parents), you might consider an optimization where you store child IDs and
parent IDs in two columns for every row.

Or if you want to stabilize / control your table relationship structures
you could have a second table that defines the "rules" as to which children
can belong to which parents, you could give IDs to the rows in those
tables, and use that to go up and down in your primary table (use that
secondary table ID as the parent ID in your primary table, in essence) -
it's kind of like enforcing a schema but on the table structures..

I hope that helps!
Steve

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Simon Riggs 2021-12-14 10:58:37 Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL
Previous Message Rob Sargent 2021-12-14 03:47:16 Re: 5 tables with 3 different relation cases