I have a question regarding facts and dimensions.
suppose I have come across a fact table fact_trips - composed of columns like trip_id , driver_id, vehicle_id, date ( in the int form - 'YYYYMMDD') , timestamp (in milliseconds - bigint ), miles, time_of_trip
I have another dim_alerts - composed of columns like trip_id, Alert_id, Alert_type, Number_of_alerts_per_type_per_trip_id, total_alerts_per_trip_id
important thing to note is that, there is one-to-many mapping between fact and dimension over here. So, a trip_id - 1 might have multiple entries in the dim_alerts table
How do I relate them? Do I use a surrogate key or what? cause the key here is that if i include alert_id in the fact table, then for every trip_id, there will multiple alert_id's , which will add more records in the fact.
The DWH necessarily gauzes the action the user takes on his phone_app while he is driving his car. The act of driving the car is called as a trip. A single trip can consists of multiple alerts.
