Retool database: how to implement cardinality and ordinality

App Building
1

When creating PK/FK relationships between tables in the Retool database, how can you specify cardinality and ordinality? I.e. if you want to allow one-to-many or many-to-many relationships.

2

Hey Richard!
With many-to-many relationships in Postgres, you'd typically want to implement a linking table. Say you have newsArticles and topics, and any given newsArticle may have several topics. You'd then implement a newsArticles_topics table with a structure of newsArticleId and topicId, and then for each topic you associate with the specific article, there's one row in newsArticles_topics.

Does this make sense :-)?

Jonathan

3

Yes, that does make sense.

Suppose I also have a news_category and I would want to force a newsArticle to have exactly 1 news_category, how would I do that?

4

Hey @Richardk!

Since the newsArticle to news_category relationship is many-to-one you might try adding a foreign key to the newsArticle table that links to news_category:

548×606 63.1 KB

So you'd handle many-to-one as a fk relationship and the many-to-many relationship with a separate table as @jonathanbredo mentioned. Does that seem like it could work?

5

Thanks Kabirdas! I started studying PostgreSQL to avoid having to ask such basic questions in the future.

1 Like
6