PA State Parks: design question
Date: Sat, 18 May 2002 04:10:28 GMT
Message-ID: <UukF8.1602$8t.918_at_newsread2.prod.itd.earthlink.net>
I have a master table called RESERVATION, and under it is a detail table called STAYS. The reservation can have multiple stays. The stay is site that the user can stay at for x number of days (arrival and departure).
Additionally, I have the concepts of:
(a) Point of sale items (inventory items/non serialized)
(b) accountable_items (inventory items with serial numbers where a status is
tracked, ie. if a gift certificate is sold and then used)
(c) permits, etc.
Each one of these items, Stays, pos items, accountable items, permits, etc. Have specific information that needs to be saved with them. The current design does not bring back ANYTHING for the reservation when its queried AFTER the fact to show what other items were included in the total price as only the STAY (child) is tied to the RESERVATION (master).
Essentially, this seems to be an invoice and the line items, but in this case, each line item can be from another table. How would this be designed?
Right now, the payment tables consist of two tables, RECEIPTS and TRANSACTIONS. A receipt can have multiple transactions, and each transaction has an allocation eid# and allocation table. This ties it back to the appropriate detail record as described above. This doesn't seem like the proper use of a foreign key either. What's the correct way to apply a receipt of money in a situation like this? Each receipt of money is essentially broken into account codes (chart of accounts) and applied to the proper accounts, but only during the payment links, with no link back to the reservation.
Is there a way to fix this mess?
I was thinking of simply adding a reservation_pos_items table that would
track any items that had money allocations when the reservation was paid
that were not STAYS. Then query these back in another detail block,
however, I'd like to take advantage of the forms built in calculated fields
for tracking TOTAL amount, balance due and amount paid for this invoice -
the current screen has a MASSIVE amount of code just to do this simple
(well, I consider it simple) task. If I go the route of calculated fields
though, I'm assuming I'll have to create some sort of VIEW of all these line
items or in fact have a line item table with the appropriate details stored
in other tables.
Does this make sense?
I appreciate any offers/suggestions! Received on Sat May 18 2002 - 06:10:28 CEST