Re: 5 tables with 3 different relation cases

Поиск
Список
Период
Сортировка
От Steve Midgley
Тема Re: 5 tables with 3 different relation cases
Дата
Msg-id CAJexoSLmREFskjOMGwvG6ghJb6CoywXr4kOfbrp0Mv75OFWguA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: 5 tables with 3 different relation cases  (Rob Sargent <robjsargent@gmail.com>)
Список 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 по дате отправления:

Предыдущее
От: Rob Sargent
Дата:
Сообщение: Re: 5 tables with 3 different relation cases
Следующее
От: Simon Riggs
Дата:
Сообщение: Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL