>>I think you got it backwards. Databases should record simple facts from which *answers* can be constructed for questions. <<

I do like that better!

>> If "Yes/No" is an answer to some question, the DBMS must allow for it be recorded so that other questions can be answered. It is closure, remember? <<

With that model, a database would already have **all** possible
answers in it before all possible questions are asked. Not possible
even in theory. And the answers are at a different level than the
facts -- the old data and meta data getting mixed together problem,
the need to constantly update the flags when the underlying facts
changes, etc.

Closure in a mathematical system assures that you can compute an
answer from facts, rules, axioms, etc. when presented with a
question.

> the need to constantly update the flags when the underlying facts
> changes,

When the flag itself is the fact, this makes very little sense.

>> With that model, a database would already have **all** possible answers
>> in it before all possible questions are asked.

Not all possible answers, but a provision to record any possible answer.

A DBMS derives new facts (query results) from a set of asserted facts (the
database). If the initial assertions are true and derivation rules are
consistent the derived facts are true (i.e. query results are correct).

Therefore as a deductive logic system, when required the DBMS must allow for
the derived facts to be recorded as needed, not that it should record all
possible derivations.

We already do this all the time using views, derived tables, CTEs etc.

>> And the answers are at a different level than the facts -- the old data
>> and meta data getting mixed together problem, the need to constantly
>> update the flags when the underlying facts changes, etc.

Conceptually the answers themselves are facts if the inference rules are
sound. It is we who assume a distinction due to limitations in our tools,
methods and languages.

Yes, the term "flag" carries certain baggage from CS history, but I fail to
see an argument to avoid a domain with two values, in general.

--
Anith

>> Yes, the term "flag" carries certain baggage from CS history, but I fail to see an argument to avoid a domain with two values, in general. <<

If it is really a domain, I have no problem. I might have problems
with a domain that has only one value, tho. The problem is that a
flag is a computation, predicate, etc. that needs to be derived over
and over from other values. To describe a box, would you store
(length, width, height) then add a column for volume? Of course not
-- it is redundant. Volume by itself would also hide information
about the three variables that went into computing it.

> with a domain that has only one value, tho. The problem is that a
> flag is a computation, predicate, etc. that needs to be derived over
> and over from other values.

A yes/no question does not have to derived / computed / etc. Do you like
lemonade? Yes. Or no. Maybe your answer changes over time. But it does
not depend on any other values whatsoever.

> To describe a box, would you store
> (length, width, height) then add a column for volume? Of course not
> -- it is redundant. Volume by itself would also hide information
> about the three variables that went into computing it.

We're not even talking about the same thing here. You are blindly grasping
for an example of something that IS computed and clearly IS NOT what anyone
in their right mind would consider a "flag"... play fair and honest, celko!

> We're not even talking about the same thing here. You are blindly
> grasping for an example of something that IS computed and clearly IS NOT
> what anyone in their right mind would consider a "flag"... play fair and
> honest, celko!

create table celko_answer_clutching (
forum_nntp_guid uniqueidentifier not null primary key,

post_author varchar(200) not null,

is_straw char(1) not null check( is_straw = 'Y' or ( post_author <>
'--celko--' and is_straw in ( 'Y', 'N' ) ) ) )
)



--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]


"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:23520CDC-93AE-4DBF-A2B7-D3E321CFD32B@microsoft.com...
>> with a domain that has only one value, tho. The problem is that a
>> flag is a computation, predicate, etc. that needs to be derived over
>> and over from other values.
>
> A yes/no question does not have to derived / computed / etc. Do you like
> lemonade? Yes. Or no. Maybe your answer changes over time. But it does
> not depend on any other values whatsoever.
>
>> To describe a box, would you store
>> (length, width, height) then add a column for volume? Of course not
>> -- it is redundant. Volume by itself would also hide information
>> about the three variables that went into computing it.
>
> We're not even talking about the same thing here. You are blindly
> grasping for an example of something that IS computed and clearly IS NOT
> what anyone in their right mind would consider a "flag"... play fair and
> honest, celko!

>> A yes/no question does not have to derived / computed / etc. Do you like lemonade? Yes. Or no. Maybe your answer changes over time. But it does not depend on any other values whatsoever. <<

But that preference is a value:

CREATE TABLE Foobar
(..
beverage_choice VARCHAR(15) DEFAULT 'water' NOT NULL
CHECK (beverage_choice IN ('lemonade', 'beer', etc.)),

Surely, you would not ask "Do you live in Texas?" "Do you live in
California?" etc. for all 54 states and territories? And don't forget
a constirant to assure that only one of the 54 flags is set to 1.

>> You are blindly grasping for an example of something that IS computed and clearly IS NOT what anyone in their right mind would consider a "flag". <<

I defined a flag as bit that is set to mark an event or set of
conditions (think of Dijkstra's semaphores and other low-level
assembly language tricks). But the event or conditions do not have to
be the agent that sets the flag. Nor does a domain with only two
values have to be a flag. In RDBMS, we want to have the set of
conditions, so we can compute the answer to the question with a search
condition:

SELECT guest_name
FROM Invitations
WHERE beverage_choice = 'lemonade';

Do you see the difference?

> But that preference is a value:
>
> CREATE TABLE Foobar
> (..
> beverage_choice VARCHAR(15) DEFAULT 'water' NOT NULL
> CHECK (beverage_choice IN ('lemonade', 'beer', etc.)),

Why? What if the company is Country Time and all they care about is
lemonade?

> Surely, you would not ask "Do you live in Texas?" "Do you live in
> California?" etc. for all 54 states and territories?

No, but if I was a company that could only sell to people in Texas, I might
ask "Do you live in Texas?" exactly once. If you live in some other state,
survey over.

> I defined a flag as bit that is set to mark an event or set of
> conditions (think of Dijkstra's semaphores and other low-level
> assembly language tricks). But the event or conditions do not have to
> be the agent that sets the flag.

But often the condition IS the flag, Joe! Let's say I have a table that
represents a set of servers in my network. At any single point in time, I
run a process that must determine which of the servers are labeled as
"Active"... why is it so wrong to have a column called IsActive or Active,
with the possible values of Y or N? Consider that I don't care about the
history, when it became the current state, etc. I just want to know which
servers are active right now. Sorry, but this is the way I would model it.
You can design it your own way if you want.

> Do you see the difference?

I see the difference, but you either don't understand my point, or are
intentionally ignoring it.

On Mar 28, 1:57 pm, --CELKO-- <jcelko...@earthlink.net> wrote:
> >> A yes/no question does not have to derived / computed / etc. Do you like lemonade? Yes. Or no. Maybe your answer changes over time. But it does not depend on any other values whatsoever. <<
>
> But that preference is a value:
>
> CREATE TABLE Foobar
> (..
> beverage_choice VARCHAR(15) DEFAULT 'water' NOT NULL
> CHECK (beverage_choice IN ('lemonade', 'beer', etc.)),
>
> Surely, you would not ask "Do you live in Texas?" "Do you live in
> California?" etc. for all 54 states and territories? And don't forget
> a constirant to assure that only one of the 54 flags is set to 1.

I agree with Aaron.
Let me try a different example. Suppose you are buying life insurance.
Suppose you need to answer a yes/no question "Have you consumed
tobacco products in the last five years?" Your answer to this
question is a fact which might be stored in the database regardless of
your actual smoking history.
In fact, your answer can be matched against your actual smoking
history, and your answers to similar questions in your medical
history. So, your yes/no answers and your actual underlying facts are
just two different kinds of facts. A database designer cannot assume
that huamns always give correct and consistent answers, this is why
both the underlying facts and your current interpretation of them
might need to be stored.

>> What if the company is Country Time and all they care about is lemonade? <<

They might only keep data on Lemonade drinkers, such as liters per
month drunk. The flag would be redundant.

>> No, but if I was a company that could only sell to people in Texas, I might ask "Do you live in Texas?" exactly once. If you live in some other state, survey over. <<

That is done at input time and no data is being kept in the RDBMS. As
you said, "survey over" and we don't bother with it.

>> Let's say I have a table that represents a set of servers in my network. At any single point in time, I run a process that must determine which of the servers are labeled as "Active"... why is it so wrong to have a column called IsActive or Active, with the possible values of Y or N? Consider that I don't care about the history, when it became the current state, etc. I just want to know which servers are active right now. Sorry, but this is the way I would model it. You can design it your own way if you want. <<

So how do I set this flag? I will be monitoring my network; each
server will have a (login_time, logout_time) pair to show when it is
on-line; the active_flag is set by the search condition
(CURRENT_TIMESTAMP BETWEEN login_time AND logout_time). Since servers
come with clocks, why would anyone want to throw away data they
collect at no cost?

Let me give you an example of a domain with two values:

CREATE TABLE BloodDonors
(donor_id CHAR(9) NOT NULL PRIMARY KEY,
blood_type CHAR(2) NOT NULL
CHECK (blood_type IN ('A', 'B', 'AB', 'O'),
rh_factor CHAR(1) DEFAULT '+' NOT NULL
CHECK (rh_factor IN ('+', '-')),
..);

My Rh factor is a fact. I can validate it with a CHECK() because it
is a very small domain. I can verify (not set, verify) it with blood
test kit. It is not set by an event; it is a simple fact.

>> Suppose you are buying life insurance. Suppose you need to answer a yes/no question "Have you consumed tobacco products in the last five years?" Your answer to this question is a fact which might be stored in the database regardless of your actual smoking history. <<

So, what happens in 2015 after I have been smoke-free for years? A
properly designed DB would have an application date and would be able
to determine when the value of that column is now "Unknown" and I
might be eligible for a discount. The data extracted from the input
question is "From 2003 to 2008, Joe Celko used some tobacco" and this
might lead to "how many packs a day?" (for my father, that answer was
4 to 5 packs; he only lived to 85).

>> So, your yes/no answers and your actual underlying facts are just two different kinds of facts. <<

Yes! That is one of my points. Flags are at a different and
derivable level of aggregation. The goal of an OLTP database is to
capture the most basic facts and not the aggregations and summaries.
Data Warehouses deal with aggregations.

>> A database designer cannot assume that humans always give correct and consistent answers, this is why both the underlying facts and your current interpretation of them might need to be stored. <<

Agreed. This is why I stress validation and verification so much.
This is why I need to get into Data Quality sometime real soon now.

> They might only keep data on Lemonade drinkers, such as liters per
> month drunk. The flag would be redundant.

Quit changing my question!

>>> No, but if I was a company that could only sell to people in Texas, I
>>> might ask "Do you live in Texas?" exactly once. If you live in some
>>> other state, survey over. <<
>
> That is done at input time and no data is being kept in the RDBMS. As
> you said, "survey over" and we don't bother with it.

Not necessarily true! What if the next question is, "Would you ever
consider moving to Texas?"

We can go on and on all day, and I can come up with plenty of questions that
can and should be modeled as T/F, Y/N, 1,0. And you can argue every single
one of them with whatever arguments you want to make up at runtime. You
will still be wrong.

> So how do I set this flag? I will be monitoring my network; each
> server will have a (login_time, logout_time) pair to show when it is
> on-line;

WRONG! I did not say online/offline; it does not have anything to do with
whether the server is "up" or not. That is something that can be detected
at runtime, anyway. Ping server. Response? Up! No response? Down! Why
should I bother storing this, unless I am interested in uptime history? (I
already explained that I am not.) Maybe I am moving the server in or out of
a cluster, or am doing maintenance on it, or maybe I am isolating it to test
the next version of my application. Stop pigeon-holing scenarios into your
pigeon-holed view of how the real world works!

A

On Mar 28, 4:32 pm, --CELKO-- <jcelko...@earthlink.net> wrote:

> >> So, your yes/no answers and your actual underlying facts are just two different kinds of facts. <<
>
> Yes! That is one of my points. Flags are at a different and
> derivable level of aggregation.

Of course not - human answers may and do disagree with the underlying
facts. The fact that you put a check on signed and dated sheet of
paper cannot be derived from your actual smoking history.

Aaron,

Take a look at Hugo's post - celko is completely ignoring because he can't
answer it.

Here's an example of a blood donor form -
http://www.bloodbook.com/form-donorpre.html - we'd all love to see how celko
would model it.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]


"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:A0FDAEBF-E1A5-40AB-AA30-C3D3A06416A1@microsoft.com...
>> They might only keep data on Lemonade drinkers, such as liters per
>> month drunk. The flag would be redundant.
>
> Quit changing my question!
>
>>>> No, but if I was a company that could only sell to people in Texas, I
>>>> might ask "Do you live in Texas?" exactly once. If you live in some
>>>> other state, survey over. <<
>>
>> That is done at input time and no data is being kept in the RDBMS. As
>> you said, "survey over" and we don't bother with it.
>
> Not necessarily true! What if the next question is, "Would you ever
> consider moving to Texas?"
>
> We can go on and on all day, and I can come up with plenty of questions
> that can and should be modeled as T/F, Y/N, 1,0. And you can argue every
> single one of them with whatever arguments you want to make up at runtime.
> You will still be wrong.
>
>> So how do I set this flag? I will be monitoring my network; each
>> server will have a (login_time, logout_time) pair to show when it is
>> on-line;
>
> WRONG! I did not say online/offline; it does not have anything to do with
> whether the server is "up" or not. That is something that can be detected
> at runtime, anyway. Ping server. Response? Up! No response? Down!
> Why should I bother storing this, unless I am interested in uptime
> history? (I already explained that I am not.) Maybe I am moving the
> server in or out of a cluster, or am doing maintenance on it, or maybe I
> am isolating it to test the next version of my application. Stop
> pigeon-holing scenarios into your pigeon-holed view of how the real world
> works!
>
> A

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:d7d504b2-9f64-40d8-a76e-dc70051b514f@8g2000hse.googlegroups.com...
>>> A yes/no question does not have to derived / computed / etc. Do you
>>> like lemonade? Yes. Or no. Maybe your answer changes over time. But
>>> it does not depend on any other values whatsoever. <<
>
> But that preference is a value:
>
> CREATE TABLE Foobar
> (..
> beverage_choice VARCHAR(15) DEFAULT 'water' NOT NULL
> CHECK (beverage_choice IN ('lemonade', 'beer', etc.)),
>
> Surely, you would not ask "Do you live in Texas?" "Do you live in
> California?" etc. for all 54 states and territories? And don't forget
> a constirant to assure that only one of the 54 flags is set to 1.
>
> SELECT guest_name
> FROM Invitations
> WHERE beverage_choice = 'lemonade';
>
> Do you see the difference?

Yes, you went from the question, "Do you like FOO?" to "What is your choice
for dinner?"

Two completely different questions.

Nice try though.




--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Sorry to correct you but you forgot one line of code:

SET ANSI_INFLEXIBILITY ON


Joe wouldn't take it any other way <g>



"Tony Rogerson" <tonyrogerson@torver.net> wrote in message
news:781DAEF8-8C8E-4805-A595-7868FF9078DC@microsoft.com:

> > We're not even talking about the same thing here. You are blindly
> > grasping for an example of something that IS computed and clearly IS NOT
> > what anyone in their right mind would consider a "flag"... play fair and
> > honest, celko!
>
> create table celko_answer_clutching (
> forum_nntp_guid uniqueidentifier not null primary key,
>
> post_author varchar(200) not null,
>
> is_straw char(1) not null check( is_straw = 'Y' or ( post_author <>
> '--celko--' and is_straw in ( 'Y', 'N' ) ) ) )
> )

--CELKO-- wrote:

> Yes! That is one of my points. Flags are at a different and
> derivable level of aggregation. The goal of an OLTP database is to
> capture the most basic facts and not the aggregations and summaries.
> Data Warehouses deal with aggregations.

Why do you assume that all flags are aggregations? Consider, for
instance, software designed to be sold to multiple businesses (rather
than used in-house at a single one), so it has customization options
stored in a one-row table, e.g. whether customer statements should list
old charges individually until paid (open item) or roll them into a
single "previous balance" amount (balance forward). This sort of
yes/no answer is not aggregated from any other facts, but chosen
directly by the user who initially configures the software.

>> 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!")

>> Consider, for instance, software designed to be sold to multiple businesses (rather than used in-house at a single one), so it has customization options stored in a one-row table, e.g. whether customer statements should list old charges individually until paid (open item) or roll them into a single "previous balance" amount (balance forward). This sort of yes/no answer is not aggregated from any other facts, but chosen directly by the user who initially configures the software. <<

Think about what you just described. Is it data inside the data model
for the schema? Nope. Configuration is SYSTEM LEVEL META DATA! You
cannot get much higher up the chain than that -- this is where
business rules, external legal requirements and stuff like that live.
It is set by the user because the database cannot configure itself at
that level. Typically, you are even beyond the Schema Information
Tables at that level.

> several tests (predicates and formulas)" as opposed "We gave John a
> higher credit rating by setting a flag and have no idea why!")

And do you think the fact that John likes lemonade should be described by
other facts in the database (e.g. what town he spent middle school in)? Or
do you think maybe that is just a fact on its own, not derived from
predicates, formulas, aggregations, or relayed from other facts in any way?

On Mar 31, 12:43 pm, --CELKO-- <jcelko...@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.

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.

On Mar 31, 2:28 pm, --CELKO-- <jcelko...@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".

>> Once upon a time there was a questionnaire which has the following question:
Do you have sex regularly? <<

LOL! That is an old Woody Allen joke about a man and woman going to a
therapist and being asked that question:
He: "Almost never, 3 times a week!"
She: "Constantly, 3 times a week!"

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:51589058-a7a2-4a1e-a9c2-46ba44f49d4c@59g2000hsb.googlegroups.com...
> >> Once upon a time there was a questionnaire which has the following
question:
> Do you have sex regularly? <<
>
> LOL! That is an old Woody Allen joke about a man and woman going to a
> therapist and being asked that question:
> He: "Almost never, 3 times a week!"
> She: "Constantly, 3 times a week!"
>
>

OK- since it seems to open-mike nite at the Improv.....

Doctor: How's your sex life?
Patient: Infrequent.
Doctor: Is that one word or two?

Bob Lehmann

--CELKO-- wrote:

>>> Consider, for instance, software designed to be sold to multiple businesses (rather than used in-house at a single one), so it has customization options stored in a one-row table, e.g. whether customer statements should list old charges individually until paid (open item) or roll them into a single "previous balance" amount (balance forward). This sort of yes/no answer is not aggregated from any other facts, but chosen directly by the user who initially configures the software. <<
>
> Think about what you just described. Is it data inside the data model
> for the schema? Nope. Configuration is SYSTEM LEVEL META DATA! You
> cannot get much higher up the chain than that -- this is where
> business rules, external legal requirements and stuff like that live.
> It is set by the user because the database cannot configure itself at
> that level. Typically, you are even beyond the Schema Information
> Tables at that level.

I suppose you can define the aforementioned one-row table as not being
part of "the schema". Shrug.

In another message, you write:

> I have no trouble with a two-valued domain; I even gave an example of
> the Rh factor in blood typing. You just do not see them very often in
> the real world.

So what's the difference between a two-valued domain and a flag? In
particular, what if the two values are Yes/No?