Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2008
    Posts
    52

    Database Design: tracking primary work and quality control activity?

    I'm designing a database to track activities performed by employees. In addition, I need to track quality control activities which are performed on work already done. My current thought on tracking work is like so:

    WorkUnit{ WorkUnitID, UserID, Activity, StartTime, StopTime, NumberOfItems }

    From this I can calculate a rate of work, which is basically the information I need. What I'm not sure about is how to handle the quality control. Part of me was thinking to create another table:

    QcUnit{ QcUnitID, UserID, WorkUnitID, StartTime, StopTime, NumberOfItems, NumberOfType1Errors, NumberOfType2Errors }

    But then I started to think that a quality control activity is still fundamentally a unit of work, so maybe I should make the WorkUnit table self-referential like:

    WorkUnit{ WorkUnitID, WorkUnitReferenceID, UserID, Activity, StartTime, StopTime, NumberOfItems }

    Where WorkUnitReferenceID can be NULL in the case of a primary work activity and will have a value in the case of a quality control activity which is qc'ing another work unit. The Activity can be "quality control".

    (I have another question about handling the attributes of the work unit such as NumberOfItems, NumberOfType1Errors, etc. and splitting this out into another table, but one question at a time)

    What do you think?

  2. #2
    Join Date
    Jun 2008
    Posts
    29
    I faced a similar question not long ago and finally decided that since it was all the same kind of thing, it should all go in the same table.
    (Of course you'd have to be careful with such reasoning, but I won't digress on the do's and don'ts)

    In your case though, your half-stated second question makes it a bit different. I think whatever option you choose, it's better to have a separate table to record errors like :
    QcErrors (*QcErrorID, QcUnitID, ErrorCount, ErrorType)

    This makes the whole error counting much more flexible.
    Now as you can see there's an issue here : if you change QcUnitID (ie: when we record QcUnit in a separate table) into WorkUnitID (ie: when we record QcUnits as WorkUnits), you want to be able to enforce the fact that such a relationship is only valid when the given WorkUnitID is in fact a QC WorkUnit and not a "normal" work unit. Which means being able to check that WorkUnitRefID is not NULL. Furthermore, when recorded as such, you'll have to retrieve the WorkUnitRefID from your QC WorkUnitID to be able to retrieve the WorkUnit where the error occured (because normatively you don't want to have the WorkUnit where the error occured recorded in the QcError table as QcError already references the QC WorkUnit which has a one-to-one relationship to the WorkUnit).

    If all that sounds OK to you, then go for recording both your QC and normal WorkUnits in the same table, as in:

    WorkUnit (*WorkUnitID, UserID, Activity, WorkUnitRefID, StartTime, StopTime, NumberOfItems)

    QcError (*QcErrorID, WorkUnitID, ErrorCount, ErrorType)
    where 1 WorkUnitID with WorkUnitRefID not NULL has many QcErrorID

    I'd say this is my prefered solution.
    Otherwise I'd do this:

    WorkUnit (*WorkUnitID, UserID, Activity, StartTime, StopTime, NumberOfItems)

    QcUnit (*QcUnitID, WorkUnitID, UserID, StartTime, StopTime, NumberOfItems)
    where WorkUnitID has many QcUnitID

    QcError (*QcErrorID, QcUnitID, ErrorCount, ErrorType)
    where QcUnitID has many QcErrorID

  3. #3
    Join Date
    Mar 2008
    Posts
    52
    Thanks for your reply!

    Quote Originally Posted by rapht
    I faced a similar question not long ago and finally decided that since it was all the same kind of thing, it should all go in the same table.
    I'm coming to the same conclusion. I started to go the other way but I began to think that the single table was the purer solution. In theory, a qc activity could itself be qc'd, and if that's not recursive fun, I don't know what is!


    Quote Originally Posted by rapht
    Now as you can see there's an issue here : if you change QcUnitID (ie: when we record QcUnit in a separate table) into WorkUnitID (ie: when we record QcUnits as WorkUnits), you want to be able to enforce the fact that such a relationship is only valid when the given WorkUnitID is in fact a QC WorkUnit and not a "normal" work unit.
    I think you are saying I need a way to avoid having QcError records for work units that are not qc units but just "normal" work units. I think the following may get around this.

    For splitting out the attributes of a work unit into a separate table, I thought about creating two tables:

    WorkUnitAttributes {
    WorkUnitAttibuteID,
    Activity,
    AttributeName
    }

    WorkUnitAttributeDetails {
    WorkUnitID,
    WorkUnitAttibuteID,
    AttributeValue
    }

    This way I could store arbitrary (integer) attributes for each kind of work activity (including the "qc" activity)

    What do you think?
    Last edited by tmpuzer; 06-17-08 at 21:10.

  4. #4
    Join Date
    Jun 2008
    Posts
    29
    Quote Originally Posted by tmpuzer
    I think you are saying I need a way to avoid having QcError records for work units that are not qc units but just "normal" work units.
    Yeah that's what I mean.

    Quote Originally Posted by tmpuzer
    I think the following may get around this.

    For splitting out the attributes of a work unit into a separate table, I thought about creating two tables:

    WorkUnitAttributes {
    WorkUnitAttibuteID,
    Activity,
    AttributeName
    }

    WorkUnitAttributeDetails {
    WorkUnitID,
    WorkUnitAttibuteID,
    AttributeValue
    }

    This way I could store arbitrary (integer) attributes for each kind of work activity (including the "qc" activity)
    It makes sense and goes down the path of generic use of WorkUnits, which is great. I'd go for that, but there's something you want to be careful about, because it could quickly get annoying :
    - AttributeValue is not typed. You could want to have string, date, number and be forced to store all as string with no means in the database to know the type of each row. If you think you might need the type at some point in the program, you may want to add an AttributeType field to the WorkUnitAttribute table and create an AttributeType table to hold that.
    - There *will* be complications when you try to JOIN WorkUnitAttributeDetails with WorkUnit on AttributeValue=WorkUnitID both because it may not be the same type and because the relationship will only make sense in the case the WorkUnitAttributeDetails match a QC WorkUnit. I'd think twice about that, but maybe it's because I'm not experienced enough to handle these situations correctly. Better ask around.

    Finally, maybe one WorkUnitAttribute could belong to more than one Activity, in which case you'd want a cross-table:

    ActivityAttributes (*ActivityID, *WorkUnitAttributeID)

    with
    Activity (*ActivityID, ActivityName)
    WorkUnitAttributes (*WorkUnitAttributeID, AttributeName)

    Hope this helps.

Posting Permissions

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