| database design [message #81399] |
Thu, 13 February 2003 09:47  |
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  |
Julie
Messages: 98
Registered: February 2002
|
Member |
|
|
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 |
Paul
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
