Forum Discussion
Data model for Search terms in web
jesusbritog That's a little more clear. One approach would be to remove your current bridge tables and include Product, Keyword and Subcategory columns in your Search Terms table. That would then mean that you would have the same search terms in your Search Terms table multiple times. You could even have Search term and "Thing" where you list each keyword, category and subcategory associated with the search term as separate rows. Your other three tables woudl simply relate on that "Thing" column. Then you only have a single many-to-many relationship between your search terms table and your fact table. That's not the end of the world, Power BI can handle many-to-many but if you want to eliminate it then you would simply put in a Search terms bridge table that consists only of distinct search terms. That would cut your total tables down from 8 to 6.
jesusbritog That's a little more clear. One approach would be to remove your current bridge tables and include Product, Keyword and Subcategory columns in your Search Terms table. That would then mean that you would have the same search terms in your Search Terms table multiple times. You could even have Search term and "Thing" where you list each keyword, category and subcategory associated with the search term as separate rows. Your other three tables woudl simply relate on that "Thing" column. Then you only have a single many-to-many relationship between your search terms table and your fact table. That's not the end of the world, Power BI can handle many-to-many but if you want to eliminate it then you would simply put in a Search terms bridge table that consists only of distinct search terms. That would cut your total tables down from 8 to 6.