Re: SQL and defining data structures

two diffrent problems are remaining:

@nathan: should I strongly avoid to use a table in an application with SQL?
yes, I would recommend this and it is very easy to follow this rule. Just create a one to one view layer for all tables and it's done. The DDL could be generated within some minutes. The benefit would be, that most changes to tables could be hidden from the application and chaging a table and bring the new fields or whatever to the application could be done óne after the other. If you would have to change key relations, especially giving a primary key more precision remains hard. To avoid this I would recommend: use meaningless key fields and define them as bigint, your customer number should be a simple column with an additional unique constraint.

@joe: I still won't create a VIEW if it's only used in one program, and in that case using externally described fields to build the data structure is perfectly fine. (additional hint using dynamic SQL)

If you are accessing tables in this construct, this would be fatal! you would have no chance to find the needed references, in case of changes to underlaying tables. If you would only use views, nothing will happen in case of changes to tables, but there remains one problem (and I've experienced this problem once, some weeks later we noticed we had aproblem and it tooks me some days to find it):

let me just tell the story:
we've had a load process for a BI application with lots of tables, having lots of columns and in a denormalisation step, we filled up some tables with dynamic SQL (reading statements or parts of them from another table). Then we used insert into ... select ... with datastructures coming from copy sources with "Type declarations", nowadays called templates.
With one minor (we where thinking) change in the application it happened, that 2 values switched their place (I don't remember wether it was in the select, or in the target table) and from this we aggregated one column in the other and vice versa and it was rather hard to find it.

since this I have some additional rules:
- use external ds instead of internal defined ds (defined in a copy source is even worse!!!), they will fit perfectly to the record layout and the compiler will verify this by every compile!!!
- if you don't have an external ds (as sometimes in the scenario of my example) don't use ds, use a list of single fields and don't use the abbreviations sql provides!
e.g. insert into (column list) valus(list of values) instead of insert into valus(list of values)

And mostly the view with e ds is more convinient.

D*B