patternMinor
How should I best design the tables and relationships, given the following rules?
Viewed 0 times
tablesthehowdesignfollowingshouldandrulesrelationshipsgiven
Problem
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
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
Solution
From what I can understand, apart from a Customer table, you'll need three tables:
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?
- 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).
- 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.
- 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?
Context
StackExchange Database Administrators Q#27796, answer score: 5
Revisions (0)
No revisions yet.