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

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? Received on Fri Apr 11 2003 - 08:24:22 CEST