🚀 go-pugleaf

Steve Kothenbeutel wrote:

> (1) several customers come into the store to purchase the same item
> unknowing of each other's desire for the same product.  They all go to
> seperate salespeople and each salesperson brings the inventory up.  Each
> customer then decides to purchase the same item  The salesperson realizes
> there is only one left.  So, he sets up the transaction.  However, the other
> sales personnel are doing the same thing.  So, here is my question... how do
> I commit the product to a sales person while he fills out pertinent
> information about the customer?  How do I ensure that the item will be there
> once all information is filled out and stored in the server?  If I use
> transactions for this, how would I implement this measure?

databse.startTransaction

CanReserve.ParamByname ('CatalogNo').Value := aCatalogNo;
CanReserve.ExecSql;
{ select onhand, committed
   from inventory
   where CatalogNo = :CatalogNo
   with cs
// with cs is DB2 for with cursor stability - this establishes a record lock
until commit
// Interbase may have another switch
}

if reserveitem.committed >= ReserveItem.onhand then
begin
   databse.commit; // commit is faster than rollback.  No changes have been
made
   raise Exception.Create ('Insufficient stock');
end
else begin
    reserveitem.ParamByname ('CatalogNo').Value := aCatalogNo;
    Reserveitem.ExecSql
{  update inventory
    set committed = (committed+1)
    where CatalogNo = :CatalogNo
}
   database.Commit;  // This commits the changes and frees the record so others
can use it
end;
//-------------------

// To convert take the reserved items out of inventory
database.starttransaction
try
for each item in the invoice do
begin
    removeReserved.Params.ParamByname ('reserveCount').value := xxx;
    removeReserved.Params.ParamByname ('CatalogNo').Value := aCatalogNo;
    removeReserved.ExecSql;
    {  update inventory
        set committed = committed - :ReserveCount,
            onhand = onhand = :ReserveCount
        where CatalogNo = :CatalogNo
    }
end;

database.Commit
except
    on E:Exception do
        database.Rollback;
        raise;
     end;
end;

Hint:  To prevent deadlock always lock/update tables in alphabetical order
within a transaction.  Why alphabetical?  It's easy to remember.

(2) lets say that I have 5 salespeople employed by this store.  One sales

> person is fired.  Within the database, there is a record of all sales
> completed by that employee.  In creating the database, should I invoke a
> relationship between the employee's id in a seperate employee's table with
> that of the transaction?  Or should I just store the identity of the
> employee in the transaction without any relationship established between the
> two tables?  If I decide to delete the salesperson from the database and if
> a relationship exists between the two tables, wouldnt that also force a
> delete of all sales made by the employee?  And if it doesnt force the
> deletion of transactions, then would the salesperson's ID field within the
> table be left empty?
>

In a commercial application NEVER delete a record except as a last resort to
fix data that has been incorrectly submitted, or as the last part of a
warehousing transaction.  Mark the records as inactive instead.  I use a
timestamp to mark the records as inactive so I not only know it is inactive but
when it was inactivated.  An employee has a hire date and a termination date.
The termination date may differ from the inactivation timestamp.  An employee
is only active if his termination date is NULL or less than the current date.

Hint: Convert all timestamps to UTC (Grenwich time) using Window's time
settings.  This will aid in synchronizing application data across time zones.

>
> (3) When packing is necessary, should I take the middleware server offline
> or can I execute a pack procedure while sales personnel have access to the
> database?

Database maintenance operations should always be carried out "off line".  If
your site is 24x7 then you may want to consider transferring transaction data
from a "current" database to a "warehouse" database whenever it is more than a
certain age.  We use two weeks as our age, and run the warehouse job nightly.
Our shop is 24x7.

>
> (4) Lets say that I have data on my sales personnel in the same database
> file as all transactions and related information.  Is there a way to lock
> tables in the database from being deleted while I execute mass deletion
> routines?

Interbase and other serious DBMS' permit databases and tables to cross file
spaces, so the concept of "file" has no meaning in this context.

Since your transaction is entirely through SQL, you can only delete a table by
submitting the SQL "delete table xxx from database".  You can't accidentally
delete a table.

Why would you ever, apart from warehousing and fixing faulty data from an
application bug, delete data from a commercial database?  Auditability
requirements (taxation, etcetera) require that the information be kept on file
for seven years, and disk space is cheap.  In Access you start to worry when
your DB gets over 100K.  With interbase I don't know what the limit is, but
each file may get up to 2 GB, and each database may span several files.  In DB2
I support a database that is 8 terabytes in size and runs about a million
transactions per hour.

With good indexing, you should be able to keep query access times down to two
to three I/O's on even the largest tables.