Joining 2 object tables via a relationship table.

[Anonymous]

Hello,

I’m a currently connecting directly to a PostgresSQL DB which uses relationship tables to connect source and destination tables.  Below, TableA (Departments) is the source and TableB (Employees) is the destination with the Rel table connecting the 2. Both table A and B contain historical data which means they have multiple rows per id representing different time periods.  As you can see it becomes complex as there are no 1 to many relationships available at first glance.

My reporting requirements are along the lines of the below which makes me think I need to either do some sort of merge which can then be grouped by Department, but I can’t work out the best way to do it.

Any help would be much appreciated.

Thanks

[Anonymous]

Thank you  - yes it seems the challenge is in designing the correct schema.

[lbendlin]

Both table A and B contain historical data which means they have multiple rows per id representing different time periods. 

Normalize your tables. Have separate tables for Departments dimension, user dimension, Dates dimension and mapping fact table