How should I best design the tables and relationships, given the following rules

database-designinformix

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?

Best Answer

From what I can understand, apart from a Customer table, you'll need three tables:

  1. Contract — holding the single-valued data about a contract, excluding receipts. It might record the latest receipt number for the contract, but that would be an optimization, storing derivable data. Primary Key: Contract Number (aka Lot Number).
  2. Contract Items — holding the 1-6 items for the contract. Primary Key: Lot Number, Lot Sequence Number. Lot Number is a Foreign Key reference to Contract.
  3. Receipts — holding information about receipts. Primary Key: Receipt Number. Foreign Key: Contract Number reference to Contract again.

A given receipt is associated with one contract; a single contract may have multiple receipts over time if it is a pawn contract (buy and sell contracts will have a single receipt).

Even if a new receipt is not issued when a pawn contract is cancelled, there'll need to be a database update of the contract or the receipt (or both) to indicate that the contract is cancelled.

Is there anything that I'm missing here?

Related Question