Im building my first star schema
Order fact table with Product, retailer. date and customer dimensions so that's fine.
im finding it hard getting my mind set out off my normal N-normal form approach. for example my products are mostly dresses which are supplied by designers.
1) should this designer information be stored in a designer table that joins to the product table which then joins to the fact table. This would start to build a snowflake schema which from what ive read should be avoided if possible.
2) should the designer info then be its own dimension table with the key stored in the fact table, if i adopt this approach i could have many dimension tables as our dresses alone come in different colors and materials,
3) or should the designer info be merged into the product table with the likes of the color and material etc.
so kind of getting lost in drawing a line in the sand as to what is a table of its own and whats not?