topic Data Modeling in Data & Schema Discussions

Data Modeling

Naruto — Wed, 20 Apr 2022 15:30:05 GMT

I have table A and Table

Table A has below data

inv_name	inv_id
1001A	10000001
1002b	10000001
AE-21F	10000001
1004D	10000002
UZ-WTS	100000002

TableB

pr_id	sub_prd_id
10000001	123456
10000001	23456
10000001	13456
10000001	121212
10000002	123456
10000002	23456
10000002	13456
10000002	121212

when i join table A(Child) with Table B(Parent) on inv_id = pr_id i'm getting i'm getting single parent record

inv_name	inv_id	pr_id	sub_prd_id
AE-21F	10000001	10000001	123456

how can i get all the sub_prd_id id details mapped each prd_name in incorta?

i'm getting it with materialised view , is there any other solution to achive it.

Re: Data Modeling

RADSr — Wed, 20 Apr 2022 16:06:44 GMT

Looking at your table definitions which columns are defined as keys?

Re: Data Modeling

Naruto — Fri, 22 Apr 2022 15:58:34 GMT

i don't have any key columns in these table.

what i'm trying to achive is if there are multiple records for a single product_id , if i join the table with inv_id(have same value as product_id) , for a single inv_id has multiple items , how could we achive it with out bridge table?

Re: Data Modeling

RADSr — Fri, 22 Apr 2022 17:05:06 GMT

Ah - sorry - my first look at the table was too quick!

I don't believe you can force the Incorta query engine to do that between the two tables.

As you pointed out you'll need an MV ( or maybe a derived table - I don't have access to an environment to do a quick test at the moment ).

You could write a SQL statement into a single Incorta table using a SQL Database source, but I'm not sure you'd see much advantage to doing that unless you truly are never going to need tables A and B.

Re: Data Modeling

suxinji — Sat, 23 Apr 2022 21:32:35 GMT

Hi @Naruto,

Here are two other solutions in Incorta:

Optional 1:

Using Incorta SQL Table.

Optional 2:

Business Schema - SQL view

This is a new feature in Incorta.

Here are the results:

For the above solutions, I'm using Incorta Cloud Release: 2022.4.0.

Please let me know if these two solutions work for you.

Feel free to let me know If you have any other questions.

Re: Data Modeling

Naruto — Sun, 24 Apr 2022 17:48:26 GMT

Thanks Suxinji for the solutions.

is there any possibility to do it with out Manual SQl?

what im looking is instead of using MV,Incorta_postges,incortaSql can we model the data(code free pipeline) to perform M:M relationship?