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

"Mike" <Star.Point_at_mcsci.net> wrote in message news:a90c0da6.0304110155.1aacad7d_at_posting.google.com...
> tblLabor (Example)
> Labor Labor Statistic
> UserID CaseID Date Hours Amount StatisticTypeID
> --------------------------------------------------------------------------
-
> Emp1_ID ABC_ID 1/1/01 1.0 25 Alpha-Microbes (count)
> Emp1_ID XYZ_ID 1/1/01 1.5 .00048 Beta-Microbes (weight)
> Emp1_ID ABC_ID 1/2/01 5.5 2 Alpha-Microbes (count)
> Emp2_ID ABC_ID 1/1/01 3.0 8.5 Alpha-Microbes (count)
> Emp2_ID XYZ_ID 1/2/01 1.5 500,010 Gama-Micorbes (measurement)
> Emp1_ID XYZ_ID 1/2/01 .25 3 People (count)
> Emp1_ID XYZ_ID 1/2/01 .25 1 Charts (count)
> Emp1_ID XYZ_ID 1/2/01 .25 5 Phone calls (count)
> ...

This last view clarifies a lot. Your problem essentially is the old design dilemma: a single heterogeneous list vs. multiple tables. I don't agree that heterogeneous list is necessarily better. First, creating a new table per each new type of measurement is not a bigger deal than declaring a new type (or should I better say "programming new type"?). Second, reports on the table are simpler than reports on heterogeneous list: for example "tell me the average Alpha-Microbes count". The only technicality, which is missing, is a foreign key constraint: a parent record should match some child in one of the measurement tables (doesn't matter which one). If we create a union view that consolidates all StatisticTypeIDs then the problem reduces to a foreign key constraint from child view to parent table. Today, constraints upon views are not supported (although, some RDBMSs allow user to declare such constraints for better optimization). Received on Fri Apr 11 2003 - 18:45:44 CEST