On Sat, 2005-07-16 at 10:29 +0700, truesaint wrote:

> > It sounds like you've been talking to COBOL'ers. I have learned that
> > they will generally complicate things all to heck rather than deal with
> > a null or a variable-size data structure.
> > In my not so humble and oft overstated opinion, you're in trouble and
> > its time to start digging. Double purposing columns is always a
> > recipe for complexity.
> LOL.
> I'm not sure about the COBOL remark (you want me to check? :), but i
> used to have the same opinion about this particular design decision
> since it's a bit counter-intuitive, but this is part of the in-house
> 'framework', and it works rather nicely and actually simpler when you
> got used to it.
>

Given that there is an in-house framework, I can see why it would be
simpler to follow the established pattern. I often have to do that too.

> The example given to on one of the argument/discussion was an invoice
> detail / line item data model. The detail table looked like this
> (simplified) :
> ...
> INVOICE_DETAIL_ID bigint not null(PK)
> INVOICE_ID bigint not null (FK)
> ITEM_ID bigint not null (FK)
> QTY integer not null
> UNIT_PRICE numeric(18,4) not null
> NOTES varchar(128)
> ...
>
> Suppose that there's a requirement that each line item can be DISCOUNTed
> according to some business rules. It can be a fixed amount, or it can be
> a percentage. Obviously there are several ways to model this.
>
> The two main contenders are :
> ... Method A :
> DISCOUNT_AMOUNT numeric(18,4),
> DISCOUNT_PERCENTAGE numeric(8,2)
> ...
> vs
> ... Method B :
> DISCOUNT numeric(18,4)
> IS_PERCENT smallint
> ...
>

I would have made a different decision, but then this decision is not
mine to make.

> One of the major problem is how to do the data entry screen in method A.
> Please keep in mind that we're using data aware controls in Delphi, so
> although mucking around with non-data aware controls can achieve the
> desired results, it's quite tedious. Providing two edit boxes will also
> confuse users.

Fair enough ... my Delphi skills are getting rusty - it's been over a
year since I had a working version of Delphi installed.

>
> Method B is a lot easier. Just provide one editbox for the amount, and
> one checkbox for the percent flag. Very simple, and our users understood
> it right away. The SQL for finding out the line item subtotal is also a
> bit simpler, with the assumption that both fields (AMOUNT and
> IS_PERCENT) are NOT NULL. If anyone has a better idea for a 'cleaner
> data model', i would love to hear it. Thanks in advance.
>
> > I gather that your views are presented by a Delphi app. Delphi's data
> > aware controls are capable of formatting the received data, provided it
> > has not been monkeyed with already. Even when using something other
> > than data aware controls, Delphi has a good set of formatting tools.
> > Nulls are a non-issue that make logic simpler rather than more complex,
> > in the long run.
> The 'common wisdom' around here is to NEVER have NULL fields if you can
> help it. Some VARCHAR fields (i.e. NOTES or DESCRIPTION) is probably ok,
> but NULLs in numeric fields (that are used for calculations, etc) is
> discouraged.
>
> > Using views is a good idea. Casting everything to strings in the view
> > in the DBMS and formatting it there is generally a bad idea, except for
> > some data migration designs (generally involving obscure COBOL data
> > types like COMP4).
> The Views are used for NOTHING else but display purposes, so I'm not
> sure i understand the problem. The data is not going to be edited or
> anything, just displayed.
>
> > First, I would change the views to return both the AMOUNT (as a longint,
> > double, or currency, which ever provides the precision nearest your
> > requirements) and the IS_PERCENT columns (as a string).
> Yes, this is the current 'plan b' workaround. Minor gripes is that this
> uses two columns, and kinda wastes valuable screen estate.

Why does it need to use extra screen real estate?

>
> > If you are using a data aware control, you would then create an event
> > method in your display field (can't remember which one now) to format
> > your text on display. The event you need to set up may have been
> > "onDisplay". I don't have a working copy of Delphi any more or I'd look
> > it up. I remember I had to dig a bit in the documentation on the data
> > aware grid controls. In this method, you would format your text for
> > display, based on the flag.
> > Alternatively, if you are using controls that are not data aware, you
> > would just retrieve the column with amountField.AsCurrency or
> > amountField.AsInteger and format it using Delphi's string formatting
> > functions, based on isPercentField.AsString.
> Probably the onGetText event. But these are not possible to implement in
> the client side due to restrictions of the framework. That's why i was
> aiming for something at the server side. Maybe I would have to resort to
> UDFs for this?

A UDF would probably be the easiest. One of the things I miss in
Firebird that I have in DB2 is the embedded REXX extensions - those are
really handy in a situation like this.

You could try doing it with embedded math, but the UDF would probably be
a superior alternative.

I am not sure if this syntax would work in Firebird, but this may give
you a start.

(
(case
when (numeric_value > 1000000)
VARCHAR(INTEGER(numeric_value / 1000000)) || ','
otherwise ''
end) ||
(case
when (numeric_value > 1000)
RIGHT('00'|| VARCHAR(INTEGER((numeric_value mod 1000000) /
1000)),3) || ','
otherwise ''
end) ||
RIGHT('00'|| VARCHAR(INTEGER((numeric_value mod 1000) / 1000)),3)
) as AMOUNT

>
> Thank you very much for the suggestions.
> regards,
> sugi.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>