Data Model for User Activity Flow

[SanthiyaMallow]

I am having a requirement where we have to ensure we design the database according to reporting needs as well.

User We are having a booking application where we are going to track user activity, I will be using power bi to prepare reports and dashboards.

Now, to track the user activity between the screen i need to link each of the events, for eg. home screen to pick up location selection screen (Pickup event) then to pickup and drop off location selection screen (pickup and drop off event) then to check fare display screen (check fare event) then to confirm booking screen (confirm booking event).

To link each search we are planning to provid the previous event id for eg, pickup event will unique id and previous_event_id (home_screen_id) similary for other events. This will help us to satisy few of the reporting needs.

I am having complexity in designing schema because this order of event is always not the same, so how do i link/establish relationship between them.

Any ideas on tracking this king of screen to screen activities for reporting needs?

[SanthiyaMallow]

@v-yaningy-msft , We are going to use postgres db. For the relational database event if we store the events in a single table, i would like to know how we should establish relationships between these events. Any idea on this?

[v-yaningy-msft]

Hi, @SanthiyaMallow 

Given the variability in the sequence of events, your approach to include a 'previous_event_id' in your event tracking is a smart move. This self-referencing key allows you to maintain a chain of events regardless of their order. Here's a suggestion on how to structure your events table in your PostgreSQL database:

1. Events Table, this table should have columns for 'event_id', 'previous_event_id', 'event_type' (e.g., home screen, pickup event), 'user_id', 'timestamp', and any other relevant information about the event. The 'previous_event_id' column is crucial as it links each event to its predecessor, forming a linked list of sorts.

2. Establishing Relationships, in a relational database like PostgreSQL, relationships are typically established through foreign keys. However, since your scenario involves linking records within the same table, you've effectively created a self-referencing relationship. Ensure that 'event_id' is a primary key and that 'previous_event_id' is a foreign key that references 'event_id' within the same table.

In short, if you have a large amount of data, relational database design is a very complex thing, requiring professionals to conduct requirements analysis, entity-relationship model design, paradigm design, index design, data type selection, table design, normalization and denormalization, security design, etc., you need to combine with the actual needs to design the database.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[v-yaningy-msft]

Hi, @SanthiyaMallow 

Based on your description, It sounds like you are trying to design a schema for tracking user activity flow in a booking application. One approach to consider is using a graph database to model the relationships between events. In a graph database, you can represent events as nodes and the relationships between them as edges. This allows you to easily model complex relationships between events and track the flow of user activity through the application.

For example, you could have a node for each screen in your application, such as the home screen, pickup location selection screen, and check fare display screen. You could then create edges between these nodes to represent the flow of user activity from one screen to another. Each edge could have properties such as a unique ID and a timestamp to track when the user moved from one screen to another. This would allow you to easily query the database to see the flow of user activity and generate reports for your needs.

Another approach could be to use a relational database and create a table to store user activity events. Each row in the table could represent a single event, such as a user moving from the home screen to the pickup location selection screen. You could include columns for the event ID, timestamp, user ID, and any other relevant information. You could then use SQL queries to analyze the data and generate reports.

These are just a couple of ideas to consider. The best approach will depend on your specific needs and the requirements of your application.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum