Re: 5 tables with 3 different relation cases - Mailing list pgsql-sql

From Steve Midgley
Subject Re: 5 tables with 3 different relation cases
Date
Msg-id CAJexoSLmREFskjOMGwvG6ghJb6CoywXr4kOfbrp0Mv75OFWguA@mail.gmail.com
Whole thread Raw
In response to Re: 5 tables with 3 different relation cases  (Rob Sargent <robjsargent@gmail.com>)
List pgsql-sql

On Mon, Dec 13, 2021 at 7:47 PM Rob Sargent <robjsargent@gmail.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

 

pgsql-sql by date:

Previous
From: Rob Sargent
Date:
Subject: Re: 5 tables with 3 different relation cases
Next
From: Simon Riggs
Date:
Subject: Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL