Amazon's data modelling question | Dimensional model for Employee check-in check out time

Design a data model to check total amount of time spent by an employee in office, considering an employee an employee can go in and out of office any number of times

[krishnakommula]

You cannot store n number of rows for multiple check in check outs in a single day with all other attributes..instead maintain a transaction table for check in check out captures.. also maintain an aggregated fact table for total check in time at a day granularity..you can easily roll it up for day, week, month year etc

[psdas]

Employee and manager relationship should not be in fact. I think It should be pulled out as a separate dimension. What if I need the check-in time for all employees under a manager 5 years back? Since I do not store the relationship history i will not be able to get this information. Please share your opinion.

[vishalkaushal]

You can not have only one Dimension for Time, for more accurate information we need two Dimension, Date and Time.

If you are thinking to have only one table, then consider where employee make multiple check in check out time and we need hour, minute and second information so for one day we would be having 24*60*60 records, which will make our table size huge.

Better convert that into snowflake schema

[Junn-qfdr]

Thanks dude, Can you upload some more Data Modelling interview questions?!

[linhng]

Nice contents but ur handwritinge very hard to read