Prev: Need help writing a stored procedure
Next: testing if value is null
From: Alex Kuznetsov on 31 Mar 2008 14:36
On Mar 31, 12:43 pm, --CELKO-- <jcelko...(a)earthlink.net> wrote:
> >> Why do you assume that all flags are aggregations? <<
>
> Not aggregations per se, but things set by events at a different level
> of abstraction in the data model. Most of my postings have dealt with
> things that should be deduced from simple facts within the schema
> ("John is eligible for a higher credit rating because he passed one of
> several tests (predicates and formulas)" as opposed "We gave John a
> higher credit rating by setting a flag and have no idea why!")

I have provided you some examples when a flag cannot be derived from
other data. You have chosen not to reply.
From: --CELKO-- on 31 Mar 2008 15:28
The example of a medical questionnaire is very appropriate for me
right now. I am getting a physical on 2008-04-01 and just had to fill
a four-page basic intake questionnaire I got in the mail.

1)The pre-existing conditions are asked as yes/no questions for the
intake form ("Do you have high cholesterol?") so that they can be
measured on an appropriate scale later in the exam (LDL cholesterol,
HDL cholesterol, and triglycerides)
2)The surgery list asks for the calendar year of the operations. Not
just yes/no, not within a range of years past, but the actual calendar
year. They want the fact, not a flag.
3)The family history also asks about the calendar years when family
members were diagnosed for heart problems, cancer, etc. Not just yes/
no, not within a range of years past, but the actual calendar year.
They want the fact, not a flag.
4)The "life style" questions are also detailed and not just flags;
they want measurements.
1.Do you use tobacco? What kind? (cigarettes, cigars, snuff, etc.)
How much?
2.Do you drink alcohol? What kind (beer, wine, liquor, etc.) How many
drinks per week?
3.Do you use caffeine? What kind? (coffee, tea, etc.) How many drinks
per day?
4.How many sex partners do you have? What genders? Animals don't seem
to count:)
5)Male and female conditions are clearly separated to avoid
conflicting data entries. One of the problems with flags is that
certain combinations might not be valid data -- "pregnant men" -- and
you need elaborate CHECK() constraints to avoid bad data. But this is
a Data Quality issue.

This sort of form is for intake only; it is not meant to be a medical
record. The actual database will contain my blood pressure, blood
type, cholesterol level and any tests indicated by the intake form --
not a yes/no flag for "do you have blood:)

Now, we are into data quality issues and the use of scales and
measurement. There standards for the acceptable levels of error and
risk in particular industries. There are measures of "fuzziness" in
data.

While all of this DQ stuff is important, it has little to do with the
use of flags in an RDBMS.
From: Alex Kuznetsov on 31 Mar 2008 16:16
On Mar 31, 2:28 pm, --CELKO-- <jcelko...(a)earthlink.net> wrote:
> The example of a medical questionnaire is very appropriate for me
> right now. I am getting a physical on 2008-04-01 and just had to fill
> a four-page basic intake questionnaire I got in the mail.
>
> 1)The pre-existing conditions are asked as yes/no questions for the
> intake form ("Do you have high cholesterol?") so that they can be
> measured on an appropriate scale later in the exam (LDL cholesterol,
> HDL cholesterol, and triglycerides)
> 2)The surgery list asks for the calendar year of the operations. Not
> just yes/no, not within a range of years past, but the actual calendar
> year. They want the fact, not a flag.
> 3)The family history also asks about the calendar years when family
> members were diagnosed for heart problems, cancer, etc. Not just yes/
> no, not within a range of years past, but the actual calendar year.
> They want the fact, not a flag.
> 4)The "life style" questions are also detailed and not just flags;
> they want measurements.
> 1.Do you use tobacco? What kind? (cigarettes, cigars, snuff, etc.)
> How much?
> 2.Do you drink alcohol? What kind (beer, wine, liquor, etc.) How many
> drinks per week?
> 3.Do you use caffeine? What kind? (coffee, tea, etc.) How many drinks
> per day?
> 4.How many sex partners do you have? What genders? Animals don't seem
> to count:)
> 5)Male and female conditions are clearly separated to avoid
> conflicting data entries. One of the problems with flags is that
> certain combinations might not be valid data -- "pregnant men" -- and
> you need elaborate CHECK() constraints to avoid bad data. But this is
> a Data Quality issue.
>
> This sort of form is for intake only; it is not meant to be a medical
> record.

The last attempt: this is not correct. In many cases your answers need
to be stored separately. An insurance companies may void a policy if
an answer is not correct. A resaercher may find it useful to match yes/
no answers against more detailed data. Once upon a time there was a
questionnaire which has the following question:
Do you have sex regularly?
In many cases "yes" meant "every month", and in many other cases "no"
meant "not every day".
From: Shuurai on 31 Mar 2008 16:22

> It is also a bad idea to use proprietary BIT data types to fake
> assembly language style programming.  SQL is a predicate language;
> that is, we discover a fact with a predicate rather than set a flag.

Unless we're actually storing a Yes/No, True/False, or some other two-
valued data; as would seem to be indicated by the "INDICATOR" column
name.

INDICATOR, as an aside, is not a reserved word in SQL Server.
From: Shuurai on 31 Mar 2008 16:28
On Mar 24, 2:40 pm, --CELKO-- <jcelko...(a)earthlink.net> wrote:
> >> The fact remains, some things are just that, flags... no further information necessary, no updating required. <<
>
> I define a flag as a binary value which is set by an event.  Can you
> give me an example of a flag that is set without an event?  

A flag that shows whether or not a person has access to a given
process is an example; we don't care when the access was granted or if
they used to have it, we care if they have it now.

> People keep trying to model yes/no questions a survey with flags.  

Go figure... trying to use a data type with two possible value to
hold something with two possible values. Outrageous!

> They get screwed up when they need more status codes such as "not answered", "N/
> A", "contradictory", etc.  As you so nicely put it: "Real world design
> and development dictates a little more flexibility than that."

For a simple yes and no question, NULL works just fine for a question
that isn't answered.
 |  Next  |  Last
Pages: 1 2 3 4
Prev: Need help writing a stored procedure
Next: testing if value is null