Need a better approach in how to design this relation

Question

I have a table fluxo, and fluxo have many fluxo_acao, but a entry in fluxo_acao can have three types, and each type have they particularities. to acomplish this, i created more three tables(fluxo_acao_mensagem, fluxo_acao_pergunta and fluxo_acao_escolher_fila), connected to fluxo_acao.

The problem is i can't select any of the three tables, from fluxo, because first i need to check wich type is and them i do the query.

I need a better approat in how to acomplish this relation. Even why i need create another two more. and this is the perfect time to change.

Please don't put things in your post other than what's needed to ask it. You could leave a comment that you cross-posted before realizing it was disallowed. Closing means answers are blocked, but you can still get votes. Please edit your posts to improve whenever you can. I think that askers can self-close immediately as a duplicate. PS Your final question is 2 questions with an OR; consider asking 1 good version of 1 of them. I voted the design issue as a duplicate. But if you research being stuck querying, it's also a faq & if you do ask, give a [mre]. [ask] [help] [meta] [meta.se] — philipxy, Jan 27 '21 at 19:11
thanks for your feedback. wasn't the general content, but the way you said. I edited in the top of the question because i was trying to avoid someone to post any answer and a comment may will missed. i'm accepting the duplecate suggestion. I'm done with this subject. — André Walker, Jan 27 '21 at 19:29

hbrgnr · Answer 1 · 2021-01-27T07:52:17.380

0

It's hard to understand what exactly these types are supposed to do, but it sounds like inheritance could help you: https://www.postgresql.org/docs/13/tutorial-inheritance.html

edited Jan 27 '21 at 07:52

answered Jan 26 '21 at 20:17

hbrgnr

371
1
11

1

Thanks for your answer. i'm checking the link. i will let you know if help me. – André Walker Jan 26 '21 at 20:38
This link is severely outdated, and I don't see how inheritance could help here. – Laurenz Albe Jan 27 '21 at 04:56
it's supported by the newer postgres versions too https://www.postgresql.org/docs/13/tutorial-inheritance.html – hbrgnr Jan 27 '21 at 07:49
i think it does help, he will have one base type `fluxo_acao`, and three types that inherit from it (`fluxo_acao_mensagem`, `fluxo_acao_pergunta`, `fluxo_acao_escolher_fila`). – hbrgnr Jan 27 '21 at 07:52
More often than not, inheritance is the wrong approach. – a_horse_with_no_name Jan 27 '21 at 07:57

score 0 · Answer 2 · answered Jan 27 '21 at 04:58

You could add the columns for all three types to fluxo_acao and use a check constraint to ascertain that the correct columns are NULL or NOT NULL for each type. Then the join would be trivial.

For convenience, you could define views on fluxo_acao for each type that show only the pertinent columns.

Need a better approach in how to design this relation

2 Answers2