Postgres Pro
Downloads
Home   >   mailing lists

Thread: 5 tables with 3 different relation cases

5 tables with 3 different relation cases

From
JORGE MALDONADO
Date:
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
Attachment

Re: 5 tables with 3 different relation cases

From
Rob Sargent
Date:
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?
Attachment

Re: 5 tables with 3 different relation cases

From
Steve Midgley
Date:

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