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

You need to read the ISO-11179 Metadata standards; you are still using that silly FORTRAN-style "tbl-" prefix and several of your other names are wrong. And please post DDL instead of your personal pseudo-code when you want people to help you; now we have to guess about keys, datatypes, constraints -- everything that is important.

A data element with a name like "statistic_type_id" is absurd on the face of it. What about the "statistic_type" to which this unique identifier belongs? Types have values; they are attributes, not identifiers. Likewise "statistics" is a class of things, not an attribute.

You have no idea what a data model is. What you are trying to do is called a "variant record"; it is an old COBOL trick to get a file (NOT a table!!) to define the fields (NOT columns!!) in each record (NOT rows!!) as it is read from left to right. Stop writing COBOL and learn SQL.

>> 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?) <<

LOOP? Gee, just what you do in a sequential file system, but not in a set-oriented language.

>> This "multi-type data" seems like an easy and functional way to
store a large number of different types of statistics. <<

It is a procedural, non-relational way that we used to do in COBOL and other 3GL languages. Please read a book on the relatuional model and pay attention to normal forms.

>> 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 [sic]? <<

NO!! A column is totally different from a field. This is absolutely basic.

>> Is there a better way to do this? Should a field [sic] be reserved
for each different type of statistic? <<

The table ought to look more like this:

CREATE TABLE LaborReport
(report_id INTEGER NOT NULL PRIMARY KEY,  average DECIMAL(8,4) DEFAULT 0.00 NOT NULL,  std_dev DECIMAL(8,4) DEFAULT 0.00 NOT NULL,  ...);

>> Each Labor record [sic] must have only one statistic type, ... <<

Why? You have not given any specs that make sense. What is this class of entities called "Labor"?

>> and each statistic must have labor, so building a one-to-many or
many-to-many [table?] seems like overcomplicating things. <<

Try posting DDL and some specs, then we can help you. But in the meantime, try to educate yourself -- you are missing the whole idea of an RDBMS. Received on Thu Apr 10 2003 - 17:27:05 CEST