cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jhenry
New Member

About the called "Link Table" and "Lookup Table"

I searched about all possible types of tables and I didn't understand 2 types, the "Link Table" and "Lookup Table".

Illustrations of each:

 

1) Link Table

 Link Table 1

 2) Another Link Table

Link Table (2)

 

3) "One lookup table for each attribute"

Lookup Table (2)

 

4) "One lookup table for all attributes"

Lookup Table (2)

 

 

So, someone understand this concepts? Someone can explain them for me?

 

Thanks a lot!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: About the called "Link Table" and "Lookup Table"

Hi @jhenry,

 

For #1, it means use relation table to manage the relation between key/value table and fact table will be suitable.


#2 is a relation table which used to link 'many to many' relationship tables.(you can't direct create relationship between 'many to many' tables)

 

#3 is a dictionary table which merge from key/value tables, you can simply use it to quick transfer key(id) to value(description). It often used to reduce database table redundancy.

 

BTW, dictionary table not need to be a real table, it can be a virtual table which used to quick lookup.(use table name and key/value to dynamic combination the search query string)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

2 REPLIES 2

Re: About the called "Link Table" and "Lookup Table"

This is a big topic. None of what I see above looks wrong to me, even the one marked wrong. My general advice

1. Opt for star Schema rather than snowflake where possible

2. Only use a lookup table if it has 3+ columns

3. Avoid junk dimensions. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Community Support
Community Support

Re: About the called "Link Table" and "Lookup Table"

Hi @jhenry,

 

For #1, it means use relation table to manage the relation between key/value table and fact table will be suitable.


#2 is a relation table which used to link 'many to many' relationship tables.(you can't direct create relationship between 'many to many' tables)

 

#3 is a dictionary table which merge from key/value tables, you can simply use it to quick transfer key(id) to value(description). It often used to reduce database table redundancy.

 

BTW, dictionary table not need to be a real table, it can be a virtual table which used to quick lookup.(use table name and key/value to dynamic combination the search query string)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors