locked
How to track who made alterations to a POS system table? RRS feed

  • Question

  • hi friend,

    I have design a product table for a POS system. This is implemented through the store's LAN network and not through web

    two scenarios are here:

    1. Track all the employees who made alteration to Products table

    2. Track only the last person who made alterations to the products table

    Here is sketch of my table design for product table

    ColumnName       DataType
    ==========================
    SKU (PK)          int
    ProductName       char(20)
    ...
    
    QntyOnStock        decimal(5,2)
    UnitPrice          decimal(7, 2)
    ...
    
    EnteredDT          datetime
    UpdatedDt          Datetime
    EmpID              int

    EnteredDT - For the first time when it was entered

    UpdatedDt - Last time updated date

    EmpID - the person who made the changes

    In this table design I can track only the person who made the last update. therefore,

    1. How do I alter this table to track a list of all the employees who made managerial changes to the product table?

    2. Is it a common industry practice to keep a Column like EmpID on a Products table? or is it should a SessionID (i.e. int value of sessions table) instead of EMpID?

    Here is my session table design

    ColumnName            DataType
    ===============================
    SesionID(PK)               int (auto increment)
    SessionValue               GUID
    EmpID (FK)                 int
    SessionStart               Datetime
    SessionEnd                 Datetime

    thanks

    I use Visual studio 2012 Ultimate and SQL server 2008 developer edition!

    Monday, December 9, 2013 12:29 AM

Answers

  • Your question cannot be answered for a number of reasons.  Foremost, your terminology is unclear (to the reader at least) as are your requirements.   You say you need to alter the table to "track a list of all the employees ...".  My response would be a question - what EXACTLY do you want to track?  I ask because it is very unusual, IME, to only track WHO without the WHAT. That is, if you are concerned about changes you want to see who changed something and what specifically was changed.  Typically, that last bit is as important than the first bit. 

    Secondly, you said "... made managerial changes ...".  That usage implies that there are non-managerial changes. 

    Third, you already have a design issue and that may be the source of your problem.  You are using the Product (or is it Products?  you are not consistent) table for multiple purposes.  Generally speaking, a row in Product should define the attributes of an individual product.  You have added columns that are not directly related to a product - information that is typically transaction-based (or derived).  This information changes over time, which is also a clue that it (your table) is not properly normalized.  You may have a good reason for this - but I mention this because it is a common error.  For a more complete example you can look at the Adventureworks sample database from MS. 

    Since I've just mentioned a time-based issue, that leads neatly to your next issue which you may not yet realize.  Over time your employees and their responsibilities change.  Manager X may decide to "move on" at some point.  What happens with the associated row in Employee?  From a different perspective, how do you intend to use this tracking information?  If you do not yet know the answer to that question, then you may run into problems later on since the answer may impact how you collect this change information (and what information you need to capture).

    Lastly you mention a decision between capturing a relationship to either employee or session.  Since you mention "this is not a web app", I have to question why such a table (session) exists in the first place.  Assuming that this table has a purpose, the appropriate response is "which relationship do you NEED to capture?" followed by "how do you intend to use this information" - which leads back to the issue in the previous paragraph.  If you capture the relationship to employee, then you probably should enforce the foreign key if you understand the implications with historical data mentioned above. If you capture the relationship with session, then you accept that you need to join 3 tables (rather than 2) to get the responsible employee.  Since there are likely to be vastly more session rows than employee rows, this may be a performance issue.  But if you need to capture the session relationship, then it is something you must live with (and perhaps work around if performance is or becomes a concern). 

    Since you are in the design phase (presumably), the trick here is not to solve any problems that you do not yet know exist.  Complete your design, evaluate it against your requirements, correct if it needed.  This is an iterative process; you will usually find that requirements need to be clarified (or defined where missing).  Once the design is complete and correct, then and only then should you worry about performance. 

    • Marked as answer by Fanny Liu Monday, December 23, 2013 1:30 AM
    Monday, December 9, 2013 3:19 PM
  • Before you reinvent the wheel, I would highly suggest you look at the existing AdventureWorks sample database, which includes a POS database. 

    http://sqlserversamples.codeplex.com/

    Also there are many accounting/POS applications already, including free downloadable versions.  Something must already exist which closely fulfills your need.

    • Proposed as answer by Fanny Liu Tuesday, December 10, 2013 9:54 AM
    • Marked as answer by Fanny Liu Monday, December 23, 2013 1:30 AM
    Monday, December 9, 2013 3:37 PM
    Answerer

All replies

  • Hi sniff_bits,

    1.   I would not alter this table to track the employees who made managerial changes, I would create another table to track that.

    2.  Yes, empid would be a good foreign key.   You always want to have a good primary key on a table for lookups.   I think your session table design seems fine.   I would ask do you really need the GUID.   I am not saying you shouldn't use it but, always give consideration before using a GUID because of how large they are, often you can find other solutions.

    Good luck!

     ________________________________________________________________________

    Please click the Mark as answer button if I answered your question, and vote as helpful if this reply helps you. Thank you!


    Monday, December 9, 2013 2:40 AM
  •   I would ask do you really need the GUID.   I am not saying you shouldn't use it but, always give consideration before using a GUID because of how large they are, often you can find other solutions.

    No I don't need a GUID : ) . actually it want meant to store the cookie values, but since this is not a web app, it really doesn't matter !


    I use Visual studio 2012 Ultimate and SQL server 2008 developer edition!

    Monday, December 9, 2013 2:54 AM
  • Your question cannot be answered for a number of reasons.  Foremost, your terminology is unclear (to the reader at least) as are your requirements.   You say you need to alter the table to "track a list of all the employees ...".  My response would be a question - what EXACTLY do you want to track?  I ask because it is very unusual, IME, to only track WHO without the WHAT. That is, if you are concerned about changes you want to see who changed something and what specifically was changed.  Typically, that last bit is as important than the first bit. 

    Secondly, you said "... made managerial changes ...".  That usage implies that there are non-managerial changes. 

    Third, you already have a design issue and that may be the source of your problem.  You are using the Product (or is it Products?  you are not consistent) table for multiple purposes.  Generally speaking, a row in Product should define the attributes of an individual product.  You have added columns that are not directly related to a product - information that is typically transaction-based (or derived).  This information changes over time, which is also a clue that it (your table) is not properly normalized.  You may have a good reason for this - but I mention this because it is a common error.  For a more complete example you can look at the Adventureworks sample database from MS. 

    Since I've just mentioned a time-based issue, that leads neatly to your next issue which you may not yet realize.  Over time your employees and their responsibilities change.  Manager X may decide to "move on" at some point.  What happens with the associated row in Employee?  From a different perspective, how do you intend to use this tracking information?  If you do not yet know the answer to that question, then you may run into problems later on since the answer may impact how you collect this change information (and what information you need to capture).

    Lastly you mention a decision between capturing a relationship to either employee or session.  Since you mention "this is not a web app", I have to question why such a table (session) exists in the first place.  Assuming that this table has a purpose, the appropriate response is "which relationship do you NEED to capture?" followed by "how do you intend to use this information" - which leads back to the issue in the previous paragraph.  If you capture the relationship to employee, then you probably should enforce the foreign key if you understand the implications with historical data mentioned above. If you capture the relationship with session, then you accept that you need to join 3 tables (rather than 2) to get the responsible employee.  Since there are likely to be vastly more session rows than employee rows, this may be a performance issue.  But if you need to capture the session relationship, then it is something you must live with (and perhaps work around if performance is or becomes a concern). 

    Since you are in the design phase (presumably), the trick here is not to solve any problems that you do not yet know exist.  Complete your design, evaluate it against your requirements, correct if it needed.  This is an iterative process; you will usually find that requirements need to be clarified (or defined where missing).  Once the design is complete and correct, then and only then should you worry about performance. 

    • Marked as answer by Fanny Liu Monday, December 23, 2013 1:30 AM
    Monday, December 9, 2013 3:19 PM
  • Before you reinvent the wheel, I would highly suggest you look at the existing AdventureWorks sample database, which includes a POS database. 

    http://sqlserversamples.codeplex.com/

    Also there are many accounting/POS applications already, including free downloadable versions.  Something must already exist which closely fulfills your need.

    • Proposed as answer by Fanny Liu Tuesday, December 10, 2013 9:54 AM
    • Marked as answer by Fanny Liu Monday, December 23, 2013 1:30 AM
    Monday, December 9, 2013 3:37 PM
    Answerer