Reference Tables

Hello,

I have two questions regarding reference tables in the area of dimensional data modeling.

1. We have a total of 581 reference areas/categories.  Which method is considered a good practice below and what are pros and cons?
Method 1: Put all the codes and descriptions columns of all 581 reference areas/categories into one master reference table.
Method 2: Create 581 individual reference tables.

2. Have you seen two schemas created for a set of dimensional data models where one schema will hold all the core/confirmed dimension tables and the other schema will hold all the reference tables.  Would you please give me a scenario where this design is implemented?  I would like to understand the rationale behind this design.

Thank you very much!
Julia

juliasuzuki wrote:Hello,

I have two questions regarding reference tables in the area of dimensional data modeling.

1. We have a total of 581 reference areas/categories.  Which method is considered a good practice below and what are pros and cons?
Method 1: Put all the codes and descriptions columns of all 581 reference areas/categories into one master reference table.
Method 2: Create 581 individual reference tables.

Neither. Codes and preferably their descriptions are integrated into the dimensions.

2. Have you seen two schemas created for a set of dimensional data models where one schema will hold all the core/confirmed dimension tables and the other schema will hold all the reference tables.  Would you please give me a scenario where this design is implemented?  I would like to understand the rationale behind this design.

Thank you very much!
Julia

I have not. I like to keep all my dimensional tables in the same schema. ETL, staging, and work tables go into a different schema.

Answers


I have two questions regarding reference tables in the area of dimensional data modeling.

1. We have a total of 581 reference areas/categories. Which method is considered a good practice below and what are pros and cons?
Method 1: Put all the codes and descriptions columns of all 581 reference areas/categories into one master reference table.
Method 2: Create 581 individual reference tables.

I would put all of them in one table because it does not sound a big and fat table and advanteage is maintance, it hard to rember 500+ name , where as in one table you search "%abc%"

2. Have you seen two schemas created for a set of dimensional data models where one schema will hold all the core/confirmed dimension tables and the other schema will hold all the reference tables. Would you please give me a scenario where this design is implemented? I would like to understand the rationale behind this design.
It better to have the dim table in one schema, because when you automate your reporting tool normally these are parameterized and one parameter is good, when dba want to do maintainence also its easy
stagging can be seperated because you don't use that in that in the reports and stagging is always (mostly) drop and reload, any typo will not impact the actual dim & fact table (stagging is closely name to dim & fact)

Thanks
Kumar