Home » Developer & Programmer » Designer » database design
database design [message #81399] Thu, 13 February 2003 09:47 Go to next message
Dee Johnson
Messages: 1
Registered: February 2003
Junior Member
1.In a banking application, how would I implement credit and debit transactions so that the running balance is computed correctly enev though there are concurrent debit and credit transactions going on.
2.How would I ensure that the zip code, city, and state code information entered in the databse is correct
(please respond in pl/sql code or describe the working of the procedure in structured english)
Re: database design [message #81400 is a reply to message #81399] Thu, 13 February 2003 11:53 Go to previous messageGo to next message
Messages: 98
Registered: February 2002
For issue 1, I would implement pessimistic locking by using the SELECT FOR UPDATE clause, which will momentarily lock the rows read to compute the current balance. A nanosecond later, a process seeking activity on the same account would wait until the records are free again.
For issue 2, I would write a plsql validation routing and call it from triggers I created for insert and update. You would have to have tables on file to support this logic. I know that there are services who provide zip code validation data and periodic updates.
I hope this helps.
Re: database design [message #81404 is a reply to message #81399] Thu, 13 February 2003 18:18 Go to previous message
Messages: 164
Registered: April 1999
Senior Member
1. The simple answer is you must first create a SEQUENCE, then define your transaction table to contain a surrogate primary key column which you will populate in a BEFORE INSERT on ROW trigger with a value from the SEQUENCE. You can then use these primary key values to determine the order in which transactions are posted. In the real world, it gets a whole lot more complicated than this because, for example a CASH deposit should be available to cover a DEBIT transaction as soon as posted, while the proceeds from an out of state check which has been deposited may not be available for use for a week. So you will probably need one or more columns for transaction codes and either form or database triggers (or, more likely, some of each) to enforce the business rules of the institution based on these codes.

2. The best you can do is
a. Get a ZIP code database from the USPS, it will list all the CITY and STATE values that are legitimate for a given ZIP.

b. Define your STATE column as VARCHAR2(2)

c. Insure that the user enters the STATE in UPPER CASE, or covert it to UPPER CASE prior to INSERT/UPDATE

d. Validate the CITY and STATE against the ZIP

Again, this does not insure that the address is "correct", only that it does not contain an impossible combination of city, state, and zip.
Previous Topic: Designer 9i to Oracle 8i
Next Topic: What is the Difference Between Oracle Developer and Designer 2000
Goto Forum:

Current Time: Sat Jul 31 06:53:25 CDT 2021