I was curious what the best way to accomplish a project I'm working on would be:I am building an Employee profile database in MS SQL 2000. It has about 20 to 30 criteria that can be updated at any time, but I need to track when a change is made and note who made the change. I also need to be able to see all criteria for one (or more) employees as it was on a given date/time.I could create 1 table and put ALL user's information into it: id INT Primary Key, EmpID INT Foreign Key(EmployeeTable), Field INT Foreign Key(AttributesTable), StringData VARCHAR(40), NumberData FLOAT, DateData DATETIME, Note VARCHAR(200), userstamp INT, datestamp DATETIME getDate()
Here, I would have to make sure there's a field for every possible attribute. Some, like Name are only one text field, but something like job_title would also need to record the date that title was effective and the id of the manager who approved it. Some attributes may require more variables requiring StringData2 or NumberData2, etc.. I could also Create a separate table for each attribute:NameTable: id INT Primary Key, EmpID INT Foreign Key(EmployeeTable) Name VARCHAR(50), userstamp INT, datestamp DATETIME getDate()PositionTable: id INT Primary Key, EmpID INT Foreign Key(EmployeeTable) Position INT Foreign Key(PositionsLookupTable), EffectiveDate DATETIME, Note VARCHAR(100), userstamp INT, datestamp DATETIME getDate()
With this option, I imagine I would need to create a Procedure which I could give a date and it would place the latest record (up to that date) from each criteria into a temp table and then SELECT * from that temp table to generate the overall reportThe final output should look something like:/* Retrieve Current Data for employee # 1 */SELECT * FROM SingleEmployee_Profile(1, getDate());EmpID | Name | HireDate | Position | PosDate | Approved By | etc...------|-------|----------|----------|----------|-------------|-- 1 | Jason | 2/4/2011 | CEO | 2/4/2011 | NULL |/* View All Employees As They Were on April 1, 2011 */SELECT * FROM AllEmployee_Profile('4/1/2011');EmpID | Name | HireDate | Position | PosDate | Approved By | etc...------|-------|----------|----------|----------|-------------|-- 1 | Jason | 2/4/2011 | CEO | 2/4/2011 | NULL | 2 | Tom | 2/4/2011 | Intern | 2/4/2011 | Jason | /* View All Employees As They are Today */SELECT * FROM AllEmployee_Profile(getDate());EmpID | Name | HireDate | Position | PosDate | Approved By | etc...------|-------|----------|----------|----------|-------------|-- 1 | Jason | 2/4/2011 | CEO | 2/4/2011 | NULL | 2 | Tom | 2/4/2011 | Coder | 5/1/2011 | Jason I will also need to come up with columns based on other calculations and cross-references such as Hire/Termination Dates (The first position date for the employee / any last date if the position matches a termination status (Fired, Quit, etc)Rather than using a Procedure/Function to build the table, are there drawbacks to using too many JOINs? (I understand there's a limit of 256 tables in Microsoft SQL 2000)Are there any major drawbacks or advantages to doing it one way over the other? Are there other solutions I may have overlooked that might be even better? I'm sure this has been done thousands of times so I don't want to re-make the mistakes others have made.Thanks all for your opinions, suggestions and ideas. 
Employee Profile Database Concept
Posted - 2011-06-07 : 19:03:56