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

From: Mike <Star.Point_at_mcsci.net>
Date: 11 Apr 2003 03:28:20 -0700
Message-ID: <a90c0da6.0304102239.765c512a_at_posting.google.com>


Costin Cozianu <c_cozianu_at_hotmail.com> wrote in message news:<b74p28$b6oc5$1_at_ID-152540.news.dfncis.de>...

Costin, thanks for your time and wisdom in answering my questions. I am re-assured by your answers that there is no inherent problem with a "multi-type" column or "union type".

The system that is using this method is working fine without any problems. I am extending the system and found the design disconcerting but could not think of any reason why it would cause problems per-say. My post to this newsgroup was an effort to double check.

I assure you that the original designer did not have much in the way of formal database training - he translated his schema to SQL without regard to its limitations! After looking at alternatives, a "multi-type" column seems to be the best solution for this problem because it is simple and *works*.

I am still interested in whether or not a single SQL statement can be crafted to sum the amount column for each type of statistic. I will post a clarified example to follow up on this.

Thanks, Mike

> 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 Fri Apr 11 2003 - 12:28:20 CEST

Original text of this message