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

[jhenry]

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

 

 2) Another Link Table

3) "One lookup table for each attribute"

4) "One lookup table for all attributes"

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

Thanks a lot!

[v-shex-msft · Accepted solution]

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

[MattAllington]

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.