Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  Database Design and Application Architecture  Design: Tracking work & quality control activity?

Author  Topic  tmpuzer Starting Member 1 Post Posted - 2008-06-16 : 18:02:16 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?