Time sheet and invoice
I am currently building a database for as small pr firm. I am creating the invoice solution and have currently got an 'invoice' table that links to 'line items' that links to 'projects' so currently i go to the invoice layout and add line items via a portal, which calls projects in. and 'invoices have a relationship with clients.
However, we invoice based on a period of time, so this solution is not working. I would like to track time for each client continually and at any given moment create an invoice with a start and end period it then calls only the line items that are relevant. I would also like it so that one line item can only belong to one invoice (i guess this can be done by making the foreign key invoice number a unit value and linking it to invoice number).
So my plan is to use the table 'line items' as a time sheet with a relationship with clients clientID = foreign key clientID etc
How can i get invoices to only assign invoice numbers at time of invoice and to filter by date of line item is between start date and end date and is this the best solution?