Is there more efficient way to keep a track of all requests made by each user with timestamp? And then filter the requests every fortnight to invoice the users?

Example DB schema

Table: user; Attributes: UserID (PK)

Table: item; Attributes: ItemID (PK)

Table: user_request; Attributes: UserID, ItemID, Timestamp

where UserId and RequestID is composite key

How should one invoice requests made by users. Is the only way to filter the stored requests based on invoice period and count the records within that period? Is there a more efficient/suitable way to invoice the requests?

What DB design are other services that charge users made on usage rather than subscription that charge on a set num of requests. Please note I'm referring to requests for data. The service would be charging users based on number of requests made for data.


For a simple solution, you can add a nullable foreign key to an INVOICE table to your USER_REQUEST. If you want to know what needs invoicing, just select everything for a given user that has a NULL foreign key.

When you create the invoice, set the foreign key to point to the new invoice.

For a more complex, but more sophisticated solution, you may want to consider an intersection table between the USER_REQUEST and an INVOICE_ITEM table. This solution will recognize and handle the situation where an item is charged on one invoice, then returned on a credit note, and possibly even re-invoice again, and so on...

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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