Re: Is multi-type data in one field normal?

mikharakiri_at_yahoo.com (Mikito Harakiri) wrote in message news:<bdf69bdf.0304102224.39b2d101_at_posting.google.com>...
> Star.Point_at_mcsci.net (Mike) wrote in message news:<a90c0da6.0304100109.581b21c4_at_posting.google.com>...
> > I have been wrestling with a table design that stores different kinds
> > of statistic data in one field ? and the type of statistic in another.
> > For Example:
> >
> > tblLabor
> > LaborID
> > UserID
> > CaseID
> > ActivityID
> > Date
> > Hours
> > *StatisticAmount*
> > *StatisticTypeID*
> > Notes
> >
> >
> > The "StatisticAmount" field is intrinsically linked to a
> > StatisticTypeID field since each number in the amount field is
> > qualified by the type field. If I want to find a sum for each type of
> > statistic I loop through the StatisticsTypes (in a tblStatisticsType
> > table), build a row set of tblLabor and sum the amount field for each
> > type. (There doesn't seem to be a single SQL statement to do the
> > summary?)
> >
> > This "multi-type data" seems like an easy and functional way to store
> > a large number of different types of statistics. It works for the
> > most part as long as all statistics are of the same data type and/or
> > can fit into a compatible data type (i.e. put ints and floats into a
> > float). Some statistics might be small numbers (.000023) and others
> > large (23,456,222), some with decimal points others without
> >
> > The questions are: What is a more *normalized* way to do this? Is it
> > normalized to store different types of data in a single field? Is
> > there a better way to do this? Should a field be reserved for each
> > different type of statistic?
> >
> > Each Labor record must have only one statistic type, and each
> > statistic must have labor, so building a one-to-many or many-to-many
> > seems like overcomplicating things. But I would like to have an
> > easier time of trying to build reports.
>
> Statistics is just an aggregate query upon some "real" data, why should it
> be linked as one-to-many to tblLabour? Or, as Costin mentioned, by
> "statistics" you just mean some properties that you want to dynamically add
> to each tblLabor record so that tblLabor becomes a heterogenous list?

Mikito, I believe the original business rule was this "Record a set of statistics for a case and record the labor hours that was needed to generate each statistic." So, if an employee counted the number of Alpha microbes in a room - record x number of Alpha microbes along with the number of hours it took to do the measurement. Reports could be generated that describe exactly how many hours were spend on each type of statistic (or measurement – maybe that's a better word than "statistic") by case and by employee.

A "statistic" has a name, a mode, status, code, notes, etc. I am simplifying the presentation of the issue by removing some columns. The example tables below are not the real tables… The table and column names, types etc are just an example... (i.e. I don't care that "tblLabor" is a "bad" or "good" name... I just gets the point across.)

CREATE TABLE Owner tblLabor (

	LaborID int IDENTITY (1, 1) NOT NULL ,
	UserID int NOT NULL ,
	CaseID int NOT NULL ,
	LaborActivity varchar (50) NOT NULL ,
	LaborDate datetime NOT NULL ,
	LaborHours numeric(18, 3) NOT NULL ,
	StatisticAmount float NOT NULL ,
	StatisticTypeID int NOT NULL ,
	LaborNotes varchar (50) NOT NULL 

CREATE TABLE Owner tblStatisticType (

	StatisticTypeID int IDENTITY (1, 1) NOT NULL ,
	StatisticEnabled bit NOT NULL ,
	StatisticName nvarchar (50) NOT NULL ,
	StatisticMode nvarchar (50) NOT NULL ,
	StatisticCode nvarchar (50) NOT NULL ,
	StatisticStatus nvarchar (50) NOT NULL ,
	StatisticNotes nvarchar (50) NOT NULL 

tblLabor (Example)

               Labor   Labor   Statistic
UserID  CaseID Date    Hours   Amount      StatisticTypeID
---------------------------------------------------------------------------
Emp1_ID ABC_ID 1/1/01  1.0     25          Alpha-Microbes (count)
Emp1_ID XYZ_ID 1/1/01  1.5     .00048      Beta-Microbes (weight)
Emp1_ID ABC_ID 1/2/01  5.5     2           Alpha-Microbes (count)
Emp2_ID ABC_ID 1/1/01  3.0     8.5         Alpha-Microbes (count)
Emp2_ID XYZ_ID 1/2/01  1.5     500,010     Gama-Micorbes (measurement)
Emp1_ID XYZ_ID 1/2/01  .25     3           People (count)
Emp1_ID XYZ_ID 1/2/01  .25     1           Charts (count)
Emp1_ID XYZ_ID 1/2/01  .25     5           Phone calls (count)

NOTE: I put the "statistic" name and its measurment mode in the "StatisticTypeID" field just for clarity - the real table just has the ID...

Users add and disable "statistics" at will. Seems odd, but it works fine.

A heterogenous list in the StatisticAmount column - yes... Users choose the "statistic" that they are working on and enter a date, hours, etc. Received on Fri Apr 11 2003 - 14:30:12 CEST