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

From: Costin Cozianu <c_cozianu_at_hotmail.com>
Date: Thu, 10 Apr 2003 14:57:23 -0700
Message-ID: <b74p28$b6oc5$1_at_ID-152540.news.dfncis.de>


Mike,

Inspite of various claims, it is kind of normal to store different unrelated data in a single field.

And don't pay too much attention to Mr. Celko's, his understanding of data types is probably limited to SQL's view of data types which is very   primitive, to sya the least. he apparently has a lot of trouble to come to terms with the boolena data type so asking him to understand the concept of a sum type is perhaps too much.

In practical problems we are confronted with supporting sum (aka union ) types (what you seem to be needing here), product types polymorphic types and other types that are not supported in SQL based products. So the mere fact that you need such a type should not bother you.

A basic principle known for decades say that you should not program in a language, you shoukld program *into* a language. And what is that saying   is that you shouldn't let the flaws and limitation of various languages be a fundamental part of how you designt he solution to the problem, you should design the solution the best you can, and then find the most suitable "translation" that fits the limitations of your particular environment.

To paraphrase the principle, you should not design yoru schema in SQL, but rather design your schema as if you had the perfect DBMS in front of you, and after that translate it to SQL. The fact that SQL's type system is limited should not prevent you from using a better type system in the abstract (including union type), and then "encode" or translate your design using whatever SQL facilities you see most fit fopr that purpose.

Whether your particular design is good or not, depends very much on some details you haven't provided. Like for example, the functional dependencies.

There's nothing wrong in principle with having a column that is a type discriminant (in your case StatisticsTypeId) and a second column (StatisticAmount) who's semantics depends on the type discriminator. Although it apparently "looks" bad it is a perfect "translation" of union type ideas who is perfectly sound and validated both in type theory and in practical languages like ML, Haskell and most advanced programming languages of today support union types.

Costin

Mike wrote:
> 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.
>
> Any suggestions are appreciated!
> Thanks, Mike
Received on Thu Apr 10 2003 - 23:57:23 CEST

Original text of this message