Is multi-type data in one field normal?
Date: 10 Apr 2003 02:09:10 -0700
Message-ID: <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*
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.
Any suggestions are appreciated!
Thanks, Mike
Received on Thu Apr 10 2003 - 11:09:10 CEST