In a Pawnshop business, customers pawn, sell or buy items. A contract which specifies the customers information, the items, and the terms and conditions is created whenever customers pawn, sell or buy items.
Up to 6 items are allowed per contract. If there's more than 6, then they have to be split up into separate contracts. The reason for this limit is because only one unique contract can exist per printed form. The form is 8.5 by 5.5 inches (half page-sized), thus only 6 item descriptions fit on each form. We legally cannot have page 1 of 2 for the same contract.
All items belonging to a contract are assigned a lot sequence number. This lot number also happens to be the contract number. The lot number increments, irregardless of whether it is a pawn, buy or sell contract. Each type of contract will maintain its own receipt sequence number. Visualize a separate pad for pawn contracts with a receipt number on the upper right corner, a separate pad for buys and a separate pad for sales.
Sells and buys are final!
However, customers can make interest payments on their active pawns, in which case the customer provides the cashier with a copy of their original receipt, along with the interest payment. Then, a new receipt for the same pawned items (i.e. with the same lot#) is issued, showing a new maturity date and the original or previous receipt gets cancelled and filed away by the cashier.
When customers redeem their pawns, no new receipt is issued. The customer signs the receipt, stating that the pawned items were returned to them, the receipt gets canceled by the cashier and filed away.
As other customers pawn items or make interest payments, new receipts are written and given to customers.
EDIT: If the pawnshop would not provide a new receipt for each interest payment, rather just updated the original contract with date interest paid, amount and new maturity date, then we wouldn't have complications! However, we have a pawn receipt number that sometimes increments, and sometimes doesn't, depending on what type of transaction operates upon each pawn.
The following is an example of pawn transactions over a period of time:
CUSTOMER TRANSACTION DATE/TIME TRANSACTION TYPE RECEIPT#
-------- --------------------- ---------------- --------
Frank Oct-28-2012, 9:00 am New Pawn, Lot#501 P12345
Mark Oct-28-2012, 9:05 am New Pawn, Lot#502 P12346
Jeff Oct-28-2012, 9:20 am New Pawn, Lot#503 P12347
...
(several other new pawns, which increment the lot and receipt numbers, and
interest payments which only increment the receipt numbers.)
Mark Nov-26-2012, 12:30 pm Pawn Lot#502, Int Pymt P12945
Frank Nov-26-2012, 12:32 pm Pawn Lot#501, Redeemed P12345 (no change)
Jonathan Nov-26-2012, 12:35 pm New Pawn, Lot#600 P12946
...
Jeff Jan-30-2013, 12:39 pm Pawn Lot#503, Forfeit P12347 (no change)
As you can see, pawn lot numbers never change throughout the life or death of the pawn, but the receipt numbers increment as different customers make interest payments on existing pawns, or pawn new items. If a customer doesn't make any interest payments or redeems the pawned items on or before the maturity date, then the customer forfeits the pawned items, (the receipt number remains the same), and the pawnshop will pull these items into their inventory.
BTW, When the pawnshop pulls a matured pawn, each item in that pawn is assigned the most current receipt number, suffixed with the item number within each contract. Then they get transferred into the pawnshop's inventory.
Thus, the second item in pawn lot number 503, having a receipt number of P12347, would be transferred into inventory with an assigned inventory number of "P12347-2", where "P" stands for Pawn, "12347" was the current receipt number, and "-2" because it was the second item within that pawn contract.
I personally wouldn't use this numbering scheme, but many pawnshops in my neck of the woods like doing things this way because they claim they can tell when was the last time a particular pawn had any activity, based on its receipt number, plus they can maintain all their pawns physically stored in chronological order, making it easier to pull them.
So, given the above rules and examples, what's the best way to design the tables and relationships for these rules, using an SQL DB like Informix, or MS-Access 2010 JetDB?
Would it be best if there's one Contract table for all pawns, buys and sells, or separate contract tables for each? How about the transactions table which operates upon the contracts table?
