Warehouse management recurring billing Database Design and Logic

Question

I'm building an application for warehouse management and billing. The storage is all pallets, so that is very simple, one pallet = $X per month. But I'm struggling to think through the best Database design for keeping track of what needs billed when. A pallet could sit for a day a month a year etc... They come and go at seemingly random intervals. One Customer may have over 1000 pallets that have all arrived at different times.
How can I best structure the database and logic to keep track of what has been billed, and what is due to be billed?

Answer 1

Table 1: Pallets (PalletID(PKey), Cost per month)

Table 2: Holdings (HoldingID(PKey), Customer ID, CustomerName, PalletsID, StartDate, ReturnDate )

You need a Java/.net based UI to pull data from these two tables, where by referring Pallets table, you can calculate amount as below

1. Customer X:

2. Start Date: XXXX:XX:XX

3. Return Date: XXXX:XX:XX

4. Get Count of months between these two dates.

5. Billing amount will be count*Cost of Pallet

Hope this helps