Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
suren946
Contributor III
Contributor III

Relationship between tables in Data Modelling

How to find the relationship between two table is one-to-one or one-many or many-to-many?

I have found below link but unable to understand, could someone explain in detail

data model relationships | Qlik Community

1 Solution

Accepted Solutions
suren946
Contributor III
Contributor III
Author

Hi Daniel,

Thank You for clear explanation.

View solution in original post

4 Replies
isingh30
Specialist
Specialist

suren946
Contributor III
Contributor III
Author

hi  Ishtdeep,

I have already found this link but unable to understand. so looking for more information

Anonymous
Not applicable

Hello, suren946!

Suppose you have these two tables below:

Countries:

Load * Inline

[ID, Country

1, EUA

2, Brazil

3, Japan

];

Cities:

Load * Inline

[City, ID

Sao Paulo, 2

Tokyo, 3

New York, 1

];

The relationship between them is clearly one-to-one, but you can prove that by creating a third table with ID's from the first one and the count of occurrences of those ID's on the second table, like below:

IDsCount:

NoConcatenate

Load ID

Resident Countries;

Left Join

Load ID,

         Count(ID) As IDCount

Resident Cities

Group By ID;

Result:

Capturar.PNG

As there is no record of IDCount greater than 1, we know the relationship between "Countries" and "Cities" tables is one-to-one.

Now, if we make this change to the second table:

Cities:

Load * Inline

[City, ID

Sao Paulo, 2

San Diego, 1

Tokyo, 3

Rio de Janeiro, 2

New York, 1

];

And run script again, we end up with this result:

Capturar 2.PNG

As now we have IDCount's greater than 1, the relationship between tables is one-to-many.

And from now, if we invert the search, counting the ID's from the second table on the first one, like this...

IDsCount:

NoConcatenate

Load ID

Resident Cities;

Left Join

Load ID,

         Count(ID) As IDCount

Resident Countries

Group By ID;

...and find IDCount's >1, we actually have many-to-many relationship tables.

suren946
Contributor III
Contributor III
Author

Hi Daniel,

Thank You for clear explanation.