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

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

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.

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.

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

Thank you very much for the suggestions.
regards,
sugi.