Re: Insert, Delete, and Update - best practices advice please

From: Mike Perry (_at_)
Date: 07/23/04 

Date: Fri, 23 Jul 2004 17:57:50 -0500

David,

First, thanks for your help, it will probably get me started in the right
direction once I sit down and determine to go there.

Next, you are correct in that these tables are more similar to a view and
technically correct about normalization, etc. as you stated.

The problem here is that I have been given a task to merge data from
different data sources and derrive an answer set that can be updated with
notes, etc. One datasource is from a SQL based Goldmine (CRM software)
installation. The core tables of this I can't touch except to SELECT from.
Inside of this database is a goldmine "custom" table which is where Goldmine
puts custom data fields and those fields are usually assigned to a key which
in this case is a column called ACCOUNTNO and has data like this "
98040707661*@!63 J.M ", that then is logically linked to another primary
Goldmine table where the GM ACCOUNTNO data is linked to our customer's
customer number. The ACCOUNTNO column is also linked to another table that
will tell me that the contact in question has been tagged as what the
management of my company decided to call a Growth Prospect.

Goldmine handles our CRM needs. Another application, which for reasons you
really do not want to get me started on, is custom written by a company that
refuses to write any code if it is not written in COBOL and runs on a Linux
box. I have ODBC Read Only access to certain "tables" in that "database"
application. The ODBC driver for that is very finicky, poorly documented,
and handles only a small subset of the SQL keywords, functions, etc.
Getting support for the driver involves a task akin to running an untrained
elephant on ExLax through untold number of hoops in the dark without leaving
any chit behind to have to clean up. For instance, it took me a year of
wrangling, pleading, and begging just to get the information I needed to get
a DSN tied to this ODBC datasource configued AND WORKING as a Linked Server
in SQL Server. Querying that Linked Server is an exercise in patience as
the I/O is about as fast as a two-legged Sloth with a massive hangover
trying to walk a straight line, and both of those legs are on the same side
of its body. This application was written by people who the word
"normalization" is not only foreign to them, it is completely non-extant on
their planet.

So, needless to say, when I have to get data from the COBOL ODBC Linked
Server, I want to get everything I am going to need off of it and into an
SQL table in as few steps as possible and do it no more often than I must -
thus the lack of normalization in my tables.

The table GrowthInv is derrived from the above datasources by doing a SELECT
... INTO statement. It includes all the data I will need from the COBOL
datasource so I don't have to go back to it again to pull together other
data. The Invoice_Nbr and Line_Nbr columns are the names that they gave
those "fields" and represent the Invoice Number of an invoice and the
itemized lines of that invoice. The Item_Nbr column is from their data and
is actually what we would probably more quickly call PartNo. I am keeping
their column names for the sake of simplicity as it is bad enough to have to
work with them in the first place, I surely do not wish to have to remember
also aliases that I have decided to use to make it look pretty and sound
actually logical.

Other information I am going to have to collect and present are such things
as Customer Name and Salesperson but I can get those easily from Goldmine's
SQL tables now that I have the COBOL Cust_Nbr.

So, the task at hand is to gather together all the itemized lines from any
invoice since the beginning of the year where the product was sold to a
customer tagged as a growth customer OR any itemized lines from any invoice
since the beggining of the year where the Item_Nbr ends with RNDM but since
some of those products could have been sold to what is also tagged as a
Growth Customer I have to be sure that I do no duplication. I effectively
do this with the SQL that created the original GrowthInv table and it is the
same SQL that creates the GrowthTemp table from which I will update the
GrowthInv table. This is why I don't have any keys assigned, there simply
is no reason to have to enforce key fields in the first place as it is
already being done before the data even gets to the table.

Once I have that information I have to add several columns to each row, one
is the Approved column, and another being two datetime columns, one for when
the line_item of the invoice was approved the first time, and the second for
when it was last changed, there is also a Notes column if the person
reviewing the data wants to make notes.

Technically all I really need in the GrowthInv table is the Invoice_Nbr,
Line_Nbr, and the added columns mentioned above for a normalized table, but
doing that will force me to have to repeatedly query the COBOL ODBC Linked
Server from an Active Server Page to present the necessary information on a
web page and I really don't want to invite timeout errors, excessive CPU
utilization and end user boredom by having to deal with the extended I/O
times involved in querying that datasource from an ASP.

Then I will have to present that data on a web page (using ASP) so the
person doing the authorizing can check one or another radio button either to
approve or deny the addition of that particular invoice line being used in
acquiring a total sales number that will be used to pay bonuses to our sales
staff at the end of the year for any given year. The thinking here is that
the Approved column is initialized with NULL and when I present the invoices
that need to be approved or denied all I have to do is search for those rows
that have a NULL in this column, once it is approved or denied the NULL will
be replaced with either a one or a zero respectively.

If the invoice line is denied I have to be able to show the salesperson on
another ASP which those rows are that were denied and then they may go and
argue a case for it being approved and who knows, they may be successful so
I have to provide a means to change that data and document why and when it
was changed - I'm told if anything is changed it will almost certainly be
changed only once.

But since this is an ongoing process throughout the year I have to be able
to update the GrowthInv table with new invoices, and there is also a chance
that an existing invoice could have some numbers or data changed on it so I
have to allow for that as well.

The person who is in charge of this sales incentive program has already
changed the criteria for what is considered growth sales and what is not
several times - I was well on my way to having the first project complete
when they threw a few details in that required a complete re-write from
scratch.

Add to all of this the fact that I am a relative novice at SQL beyond your
most basic SELECT statement and the fact that I have, before this past
month, never written an Active Server Page beyond copying an example out of
a book and you might imagine that I am a bit overwhelmed and somewhat in
over my head with this project. I'm going to have to do a lot more of this
stuff in time to come however so I guess I have to start somewhere.

sooooo, for now please do know that I appreciate your comments on what is
wrong with the schema, etc. but there is some reasoning in it being the way
that it is which is then added to the fact that I am likely to make mistakes
as I go here so be gentle please.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:rtudnX5Y34EGGZzcRVn-ug@giganews.com...
> First things first. Your table design - you haven't finished it. Despite
> what you say about (Invoice_Nbr,Line_Nbr) being unique those columns are
> nullable, as are all the columns, so the table doesn't have a primary key
at
> all. A "Line Number" is anyway a strange concept to record in a database.
I
> would have expected the key to be (Invoice_Nbr, Item_Nbr). A Line Number
is
> surely meaningful only on a printed invoice.
>
> It also seems that this table isn't in Third Normal Form. Do you really
have
> different customers and dates on the same invoice? Price_Extension is
> presumably a derived column that doesn't belong at all.
>
> My point is that what you have created is a "report" or "view" of your
data
> and actually to materialize that as a table is probably unnecessary, weak
> design and creates the headache that you are now facing of how to keep it
up
> to date. Have you considered creating a view to return the data in this
> format? Presumably your temp table would be populated from some query that
> already returns the data like this?
>
> Assuming you did want to maintain this data as a table, I don't understand
> why you would use a temp table to do it. That just creates an extra step
to
> move around even more redundant data. You haven't shown us how this Temp
> Table gets populated so I can only give a generic example of how you can
> replicate changes between two similar tables:
>
> DELETE FROM TargetTable
> WHERE NOT EXISTS
> (SELECT *
> FROM SourceTable
> WHERE keycol = TargetTable.keycol)
>
> UPDATE TargetTable
> SET
> col1 = (SELECT col1 FROM SourceTable WHERE keycol = TargetTable.keycol),
> col2 = (SELECT col2 FROM SourceTable WHERE keycol = TargetTable.keycol),
> col3 = (SELECT col3 FROM SourceTable WHERE keycol = TargetTable.keycol),
> ...
>
> INSERT INTO TargetTable (keycol,col1,col2,col3, ...)
> SELECT S.keycol, S.col1, S.col2, S.col3, ...
> FROM SourceTable AS S
> LEFT JOIN TargetTable AS T ON S.keycol = T.keycol
> WHERE T.keycol IS NULL
>
> Hope this helps.
>
> --
> David Portas
> SQL Server MVP
> --
>
>