I’m building a report for an intranet site similar to Facebook.
The primary purpose is to give stakeholders a sense of how their specific posts are doing. For example, the number of “Seen-by” (views), comments and reactions.
I’m running into a challenge trying to structure the correct data model in a star schema format since it seems I have multiple fact tables. In the attached screenshot, the tables on the top row are all the dimensions and the bottom 3 tables are the fact tables (There is one fact table for “Seen-by” which isn’t there yet in the model but will be added in the future. It will show who saw the post and on what date.
IMO the primary fact table is the “Posts” and the other three “Seen-by”, “Reactions” and “Comments” are related to the Posts but again I’m not sure how to structure this. FYI, all fact tables have a “Post-Id” column.
Any recommendations on how I could create the correct relationships here?
