Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2016
    Posts
    2

    Unanswered: Database table setup to capture current data and historic data

    As a project to help me learn PHP and MySQL I am building an employee recognition website. What I want to be able to do is have a User table which keeps an employee's current information such as name, department, job title, supervisor, etc. I want another table which will store all recognition date, such as who gave it, who received it, the message, date, etc.

    The part I am unsure of is I also want to capture within the recognition table user data (department, job title, etc) as of the date when the recognition was received. So do I want to make the recognition table have a bunch of columns to capture this data at the time of recognition or is there a better way to do this?

  2. #2
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    So, you want to freeze-in-time the state of the Employee table values when Employee_Recognition transactions are generated.

    Personally; I'd create a separate Employee_Hist table with an auto increment primary key, then store that primary key value on the Employee_Recognition table. This violates normalization rules but I find it to be cleaner in the long run.

    To implement the historical copy; the simplest method is to explicitly name each column. The downside is future maintenance as columns are added to Employee.

    There are methods for cloning rows using "*".

  3. #3
    Join Date
    Jul 2016
    Posts
    2
    Thanks for the guidance, that makes a lot of sense.

    So I think what I will try anyway is to setup two tables as outlined below. One would be a simple User table to house basic information and another User History table which would store the guts of what I want. I will put a user history id column on the User table which would point to the newest entry for that employee in the History table, which would make it the current information. Then in the Recognition table I could have the User History Id that corresponds to when the recognition was created.

    User Table - id(primary key), username, password, user history id

    User History - id, user id (key from user table), employee id, first name, last name, email address, business unit, home department, manager name, manager email


    I know this was just a rehash of what was already said but it helps me solidify it in my head. If anything about this seems wrong please let me know!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Aside from say marriage (or divorce) what other event would trigger a name change
    , even if it does do you need to track the change
    What do you think the reason to store user history id in user table
    If this is corporate then its unlikely the email would change in my experience, even if it does do you need to track the change
    Should business unit and department justify their own tables.
    Presumably a manager is also a user, so why do you think you need to store the manager name AND email in tbe user history. Presumably a FK to the user table would be fine. If the manager changes their dmail do you plan on changing all rekevant tows in the user history table

    Read up on normalisation
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    In general, Normalization says that you store stuff together that has the same "frequency" in relation to the real world. So; 1-to-1 relationship between an employee and their static info (name, address, SSN). If you keep accumulators (like YTD salary), that would have the same frequency and it makes sense (usability-wise) to also store them in the same table.

    Employee change history is 0-to-many relationship. 1 employee could have zero, 1, or 5, or 5,000 change records.

    Employee_events (marriage, move, position change, etc) also have a 0-to-many frequency relationship to the physical employee "object". They may even have the same "Frequency" as the "Change Hist" but I doubt it (typo corrections, etc). I think the "event" belongs in its own table, although its ID could be stored in the "Change Hist" table so that changes related to an event could be tied together.

    SQL can very cleanly make full copies of all columns in the tbEmployee table. Adding additional columns (like timestamp, EventID) in addition to the tbEmployee ones that are being cloned is easy, but you must include all the tbEmployee columns unless you want to name every column to be logged; and you want to maintain this script every time you add columns.

    Example:

    Code:
    CREATE TABLE tbEmployee (
      EmployeeID int(11) NOT NULL AUTO_INCREMENT,
      Empl_Name varchar(50) DEFAULT NULL,
      Empl_Addr1 varchar(50) DEFAULT NULL,
      Empl_Addr2 varchar(50) DEFAULT NULL,
      Empl_HmPhone varchar(15) DEFAULT NULL,
      PRIMARY KEY (EmployeeID)
    );
    
    CREATE TABLE tbEmployee_Hist (
      UpdateID int(11) NOT NULL AUTO_INCREMENT,
      UpdateTS datetime DEFAULT NULL,
      EmployeeID int(11) DEFAULT NULL,
      Empl_Name varchar(50) DEFAULT NULL,
      Empl_Addr1 varchar(50) DEFAULT NULL,
      Empl_Addr2 varchar(50) DEFAULT NULL,
      Empl_HmPhone varchar(15) DEFAULT NULL,
      PRIMARY KEY (UpdateID)
    );
    
    --  At this point, insert some sample-data then proceed.
    
    -- This makes a clone of an Employee row with a time stamp and sequencer.
    INSERT INTO tbEmployee_Hist 
    SELECT
      null,
      NOW(),
      *
    FROM tbEmployee
    WHERE EmployeeID = 3;
    Because the column "UpdateID" is auto_increment, it will be auto-assigned a unique value as it gets inserted.

    The strength of this approach is the "*". As you add new columns to tbEmloyee and tbEmployee_Hist (that of course, must be kept in-sync), you don't have to revisit this query.

    Its wasteful, but there usually aren't that many employees and static-info changes aren't that common so SQL can easily handle the load. On average, an employee might have 1 change per year. No biggie.

    Sidebar: Normalization rules are made to be broken as practicality demands. In general you never want to store the same data in 2 places but IMHO, a historical log is an exception. Besides; the column that changed isn't really the same value being stored twice.
    Last edited by vich; 07-23-16 at 00:40.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •