Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
I think this is a better fit for dba.stackexchange. It's a bit too localized for SO. –  lc. Oct 29 '12 at 3:58
2  
This is a legitimate design/programming issue, which needs to be properly figured out by experienced SQL DB developers! –  FrankComputerAtYmailDotCom Oct 29 '12 at 4:00
    
There is a big difference between Informix and MS Access 2010 (ACE btw, not Jet for 2010). In MS Access, a lot of what you describe would be controlled by code. The tables could be simple enough, but I would suggest a surrogate primary key for the transaction table as you do not seem to have a suitable natural key. If you want more infomation on an Access scenario, I suggest you post again on SO with suitable tags. I am not sure where would be best for Informix, I see there are only 5 followers here. –  Remou Oct 29 '12 at 11:12
add comment

migrated from stackoverflow.com Oct 29 '12 at 10:31

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 2 down vote accepted
+50

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?

share|improve this answer
    
This makes sense! So what you're saying is that I would have to create a trigger which updates the receipt column in the contract table, everyytime a new transaction operates upon a given contract? –  FrankComputerAtYmailDotCom Oct 31 '12 at 2:26
    
Would there be a problem with the conditional incrementing of receipt numbers in a multi-user scenario? –  FrankComputerAtYmailDotCom Oct 31 '12 at 2:37
    
If you store the most recent receipt number for a contract in the Contract table, then you'd need to think in terms of a trigger to update it. Alternatively, and I'd do this until I demonstrated that there was a problem, I'd just look for the most recent receipt number for the contract when I needed it. Assuming you use either a SERIAL (or BIGSERIAL) column or a SEQUENCE, there'll be no problem with incrementing receipt numbers. Each time you create a receipt, it will be given a new (serial) number, distinct from any other, regardless of concurrency issues. –  Jonathan Leffler Oct 31 '12 at 2:48
1  
Why would there be three separate counters? That's ... less than entirely sensible. They're all contracts; they don't need to be sequential within a particular type. Oh well, the customer (pawn shop owner, I suppose) is always right. Then you definitely use sequences. One sequence for Buy Contracts; one sequence for Sell Contracts; one sequence for Pawn Contracts. Your primary key on Contracts becomes a composite of type (P, S, B) and the sequence value from the relevant sequence; your foreign keys become composite keys. It makes life unnecessarily complex, but it can be done if you insist. –  Jonathan Leffler Oct 31 '12 at 3:11
1  
You've omitted an entity from your descriptions, then. There is a journal table of some sort needed, which identifies actions taken on objects in the contracts and/or receipts table (and other salient information, such as employee, customer, contract, amount of money, etc). You say you're adding an entry, but it isn't a new receipt, and it isn't a new contract (and it definitely isn't a contract item), so it must be something else — a journal entry. –  Jonathan Leffler Oct 31 '12 at 3:32
show 13 more comments

I would have three tables: one for contracts (containing the contract number and details on the item to which the contract is tied), one for customers (containing the customer's personal information), and one for transactions (linking the two via a many to many relationship, and also containing the transaction date, transaction type - though you might want to make separate tables for each type, it's up to you - and amount of money involved).

share|improve this answer
    
but, then how would you handle the conditionally incrementing of the receipt numbers? –  FrankComputerAtYmailDotCom Oct 29 '12 at 4:08
    
Do they increment per contract, or globally? If it's globally, no problem - just use an autoincrement field or whatever your database has for that purpose. If they are per contract, that might be a bit trickier! –  ekolis Oct 29 '12 at 4:10
    
see more detailed explanations in edited question. –  FrankComputerAtYmailDotCom Oct 29 '12 at 6:40
    
Oh, OK! I guess you could use an on-insert trigger to populate the receipt number, or a computed default, if your database allows you to query other tables when setting a default value. –  ekolis Oct 29 '12 at 10:50
    
transaction is not the right term, especially for a database application. the correct modeling term is "order", as in "sales order" and "purchase order" –  Neil McGuigan Oct 29 '12 at 21:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.