CODEWITHSUNDEEP
sqldatabaseinventoryrecurring-billing
bairdmar
bairdmar

Reputation: 131

Warehouse management recurring billing Database Design and Logic

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?

Upvotes: 2

Views: 243

Answers (1)

Dheeraj Kumar
Dheeraj Kumar

Reputation: 4175

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

Upvotes: 1

Related Questions