Re: Naming conventions for special database objects

--CELKO-- wrote:


> (Warning: my first Masters was in Math).  I don't use them at all.
> SQL does not have the concept of Boolean values or flags. The search
> conditions and CHECK() constraints are *predicate* logic.  We discover
> the answer to a question (yes/no) by looking at *scalar* values in
> *predicates*.  That means we don't set a flag for "is_foolicious" in
> the data; we use a predicates that tests atomic facts for the
> conditions that answer the question (if we can) -- WHERE foo > 12.45
> AND lick = 'salty'.

That's not the kind of flags I was talking about; say we need to track 
expenses and some expense types have an additional field to be required 
in the form. I might add a column in the expense_types table to indicate 
whether a datum is required.

The same way, if we write a social network where kids register their 
quigzmo collection, the "races" table might have a has_tail column -- to 
avoid asking tail lengths for races that have no tail.


> Flags don't expand.  A "simple" yes/no survey question includes "Not
> answered" and "Not Applicable" in the real world.  Preferences come in
> degrees ("Rate Squid Ice Cream on a scale of 1 to 5 Stars").

And with a clever trick we might change has_tail in minimum_tail_length, 
where accepted values are 1 and NULL... I'm not sure I like that :)


> This is why we say that a column is a *scalar* value -- it is a
> measurement on a scale in a particular unit.  What do Boolean flags
> measure?  They don't measure anything.

A way to avoid the has_tail flag might be to explicitly state the subset 
of tailed races... with

CREATE TABLE tailed_races (
     race_code VARCHAR(10) PRIMARY KEY REFERENCES races
);

but I think it might not be convenient to grow tables like that.
Two tables of "features" and "race_features" might be overkill as well - 
or not.


> It is possible to have a scale with two values; my example is + and -
> for the Rh blood factor.  That is a very specific physically
> verifiable fact on a well-defined scale.  Very often,you can capture
> the date of an event instead of a flag --"****p_date" versus
> "is_****pped" for example.

I understand what you say and I appreciate the theoretical basis and the 
practical implications, but I  lack some experience in customs. I seldom 
work in a team, I design a schema and take several months to write an 
application on it; by the next time I design the next schema I will have 
my mind full of javascript and CSS.


Thanks