Hi all,
I'm designing my first data warehouse database and have a question that maybe someone can help me with. I'm not sure if normal relational database design is the best usage for designing a data warehouse database.
I have a product table with about 19 columns that should refer to other dimension tables. Some of these columns refer to another table for code and description. For example, [brand code], [season code], [theme code].
Some columns are related and need to be combined, like [pricetype code] and [pricetype group code], where the product is assigned to a group and a code in that group. All these have a description in the related table.
For data warehouse design (going to use Azure DW in a later stage), do I make foreign keys from the product table to each related table and create a view for data mart or do I add the description of each code (like brand code, brand description) to the product table? The product information could later be used to filter or group sales or stock information, like how much have we sold for a particular brand, season or theme.
To give you an idea of the database size, we have about 100.000 products and 400 million product transactions.
Thanks