DenStudent DenStudent - 1 year ago 41
SQL Question

Solving a many-to-many relationship

Background: A company has over 5000 employees and has buildings placed in different cities. 1 Employee can be working in 2 or more of these buildings depending on the day/project. The company does not keep a track of on which day someone is in what building. They only have one sheet with all Employee data and location data. This location also includes a start date of on which date the employee started going to that location. It looks like the following:

EmployeeID | FirstName | LastName | email | BuildingID | Buildinglocation | startdate
1 | John | Gates | JG@.. | 2 | New York | 01-01-2015
1 | John | Gates | JG@.. | 1 | Paris | 01-05-2015
2 | Bill | Jobs | BJ@.. | 2 | New York | 01-01-2016
3 | Carl | Davis | CD@.. | 3 | London | 01-11-2015

I need to turn this into multiple tables to create a datawarehouse. What I was thinking about was a table for
and one for
. This however, would be a
many to many relation
, so I wanted to add a
bridge table

The question is: Where do I save the
? Should I add it in the Bridge table? How do I populate it with the
in that case? Or should I just leave it in the
location table
, making the relation between
a 1-n relation?

Answer Source

This is what I would do:

I would put the startdate in your Bridge table as the employee and location would be made before they are joined together which then the startdate is populated. The location will only exist once, so by creating a separate table for it, you are doing less work and saving memory. Adding a constraint to startdate in your bridge table so that when a new row is added, it must have a date /auto populate will ensure that you will have the best data possible If that makes sense