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

71062.1056_at_compuserve.com (--CELKO--) wrote in message news:<c0d87ec0.0304100727.1c2a2a12_at_posting.google.com>...

--CELKO--, in light of your tone, I have to say - what a diatribe of self-rightness! Formalism is only *good* when you need it, otherwise its just extra work...

> 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.
>

Excuse me if I use Hungarian! I never knew that the "tbl" prefix came from FORTRAN as you seem to know…

Seems odd that you even need to waste time/words on the completely irrelevant parts of an *example* table… Should we use -snip- to make you happy?

>
> 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.
>

Doh! A "statistic" in this example is just a number! Is "StatisticID" or "ThingieID" better for you sense of naming? I did not list the "columns" of "tblStatistics" where you might find your types, values and attributes… Was this important? (Sorry I must use the "tbl" that's the way the tables were named.)

> 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

Sorry, I have never even see COBOL like you have… Wow, a "variant record"...

> 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.
>

Excussseeeee me if I used the word "field" instead of "column". Excuse me if I used the word ‘row" instead of "record". There is only a difference when you need to talk formally. But, you did know what I meant [sic]!

> >> 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.
>

Well, I don't see you making a suggestion just a criticism – that's easy...

> >> 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.

So you say... and this is real important because... We might slip into the COBOL zone...

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

Labor is time doing something. A "statistic" amount is recorded during labor. That is the spec! I put an ActivityID, Date and Hours (time) in my "personal pseudo-code". Seemed obvious...

> >> 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.

Boy, --CELKO--, have you ever had to work with anyone else's design? Or do you just stay in an ivory tower? That little "tbl" really got you going... Received on Fri Apr 11 2003 - 01:17:12 CEST