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

"Mike" <Star.Point_at_mcsci.net> 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.)

Did you try:

SELECT StatisticTypeID, SUM( StatisticAmount ) AS StatisticTotal FROM tblLabor
GROUP BY StatisticTypeID
;

> 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?

That depends. Are you writing an application that allows users to define the statistics they gather? Or are you writing an application that gathers a couple of unrelated statistics?

> Is it
> normalized to store different types of data in a single field?

One would prefer to have polymorphism and subtypes for this, but the issue is independent of normalization, which relates to decomposition by the project operation and recomposition by the join operation.

> Is
> there a better way to do this?

Nobody can really truthfully and credibly answer this question in a usenet newsgroup. Whether a particular way is better will depend on the full set of business rules, which nobody on usenet would have time to properly consider even if you tried to list them.

> Should a field be reserved for each
> different type of statistic?

Perhaps, perhaps not. Another question you need to ask is: Should a table be reserved for each different type of statistic? Received on Thu Apr 10 2003 - 11:32:24 CEST