Re: Is multi-type data in one field normal?
Date: Fri, 11 Apr 2003 17:07:01 -0400
Message-ID: <2KGla.351$z%7.34408487_at_mantis.golden.net>
"Mike" <Star.Point_at_mcsci.net> wrote in message
news:a90c0da6.0304110045.21bbd3b5_at_posting.google.com...
> "Bob Badour" <bbadour_at_golden.net> wrote in message
news:<0Vpla.253$Ck4.22670428_at_mantis.golden.net>...
> > "Mike" <Star.Point_at_mcsci.net> wrote in message
> > news:a90c0da6.0304101254.7b97219a_at_posting.google.com...
> > > "Bob Badour" <bbadour_at_golden.net> wrote in message news:<osbla.229
> > > $DW2.20351002_at_mantis.golden.net>...
> > > > "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.)
> > > >
> > I see the issue. You want all statistics listed regardless of existence
in
> > tblLabor. You will need to query tblStatisticsType to get the full list
of
> > StatisticTypes.
> >
> > SELECT StatisticTypeID
> > , ( SELECT COUNT(*)
> > FROM tblLabor lab
> > WHERE lab.StatisticTypeID = stat.StatisticTypeID
> > ) As StatisticTotal
> > FROM tblStatisticType stat
> > WHERE Enabled
> > ;
> > Your scalar subquery works! I changed COUNT to SUM for my purpose and > I get a null value for the stats that don't have labor - exactly as it > should. Thank you!
D'oh! Of course, SUM is the aggregate operation you wanted. (What's worse is getting it right would have saved me typing had I cut&pasted it from above.)
> > > > > 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.
> > > >
> > >
> > > The word "polymorphism" gave me some pointers to track on this issue.
> > > I found that "foreign key association" might be a good name for this
> > > odd method.
> >
> > While it may be necessary to learn the new jargon for object oriented
> > programming (ie. 'object pattern names') if you need to communicate with
> > teams of programmers who have jumped on this latest bandwagon, I suggest
you
> > first learn to think using sound theory backed concepts and terminology.
> > Foreign keys are a relational concept involved with referential
integrity,
> > and as such have some impact on normalisation. Actually, fully
normalized
> > designs replace several types of constraint checking with foreign key
> > referential integrity. The result of tacking 'association' onto the end
> > 'foreign key' is meaningless nonsense invented in an ad hoc unprincipled
> > manner.
> > > I found "foreign key association" here: > http://www.objectarchitects.de/ObjectArchitects/orpatterns/index.htm > And here:
http://www.objectarchitects.de/ObjectArchitects/orpatterns/MappingObjects2Ta bles/ForeignKeyAssociation/
That's where I thought you found it. My suggestion remains unchanged: go for theory and recognize fads for what they are.
> But, I really don't care what the method is named - "multi-typed" > works for me. I also like "union type". Polymorphism is ok to...
Polymorphism is the general concept. "Union type" is one of the types that requires/implies polymorphism and is probably the exact sort of type you would want for this application.
"Multi-typed" would be all well and good except it means something different in computing: http://portal.acm.org/citation.cfm?id=203095.203097
Jargon has its advantages and disadvantages. Combined with faddism, jargon can pollute the vernacular with essentially meaningless synonyms, and computing seems dominated by faddism. Practitioners of a specialty or trade need some jargon to communicate efficiently and precisely with other practitioners. A big chunk of conceptual analysis is learning and documenting a business' jargon.
> > > - or even mention (like
> > > the temporal issue) and that maybe there was a recommended construct
> > > kicking around the newsgroups that I could not find.
> >
> > Which temporal issue? There are many issues, and time is a complex
subject.
> > Relational theorists with three decades of experience have only recently
> > started proposing satisfying solutions to the problems.
> > > > I was referring generally to all temporal issues with SQL RDBMS's that > invariable get left out of books on these relational databases. > Normalization is covered, the ins and outs of building a schema, SQL > statements etc. But what about saving the history of customer > addressed as they change over time? Many SQL RDBMS books simply have > no words on this issue.
As I said previously, experienced and knowledgeable relational theorists have only recently started proposing satisfying solutions to temporal problems. At present, no consensus exists for how best to deal with temporal data, and no products support the latest recommendations. Without a concensus and with no language support, SQL books have little of worth they could say. To summarize, temporal data is a very big subject one could not begin to effectively address at this time in a general book on SQL.
It's not for any lack of thought on the subject that the general books are silent. I know that doesn't give you an easy ready-made answer, but at least you can console yourself for not having an easy ready-made answer of your own--it's not a personal failing (far from it).
> > > On the other hand doing
> > > this seems odd. I intrinsically prefer to find the number of people
> > > in a People field and number of Alpha Particles in a AlphaParticles
> > > field - not both listed in the same field!
> >
> > This is what derived relations like views and snapshots are
for--presenting
> > an underlying design in the manner most accessible to a user or an
> > application.
> > > Cool comments, thanks again for crafting that SQL statement, Mike
You are very welcome. I hope it inspires you to explore more inventive SQL queries of your own before resorting to loops and applications. Received on Fri Apr 11 2003 - 23:07:01 CEST