Hey All,
I have a quick question regarding my database design. I am developing a database which tracks Employees in various different positions across an organization. I have opted for a many-many between EMPLOYEE and POSITION due to the requirement that historical records are kept for each Position and the Employees that have previously occupied it.
There is also a new requirement to track Candidates being considered for each position. My thought is that this many-many will handle Candidates well also. I plan on having all Candidates added to the same table, and have the DB recognize them as candidates if the record's StartDate is null.
Any feedback on whether or not my reasoning is sound for going with a many-many would be appreciated. I know many prefer to use HISTORY tables, but I think that in my case this makes sense?
Thanks!
Skid