SQLite table design question

Question

I'm working on a program that deals with invoices for services done. I'm wondering what is the best way to layout a table of invoices which can have one or more tasks for each invoice. So far the invoice consists of:

Invoice Table
| InvoiceNumber | Department | DateOfBilling |

Now, I need to somehow store the jobs associated with that invoice. Each job has the the following variables:

Job
| Date | ServiceDescription | Hours | Cost |

(Hours and cost are not always a straight multiply requiring both variables)

I'm new to working with SQLite and databases in general, but these are the ideas I've come up with. One solution would be to add a column after Date of Billing that contained a TEXT or BLOB entry that was a formated string that would need tokenizing when pulled from the DB. For example:

| DateofBilling | Date;ServiceDescription;Hours;Cost!Date;...

I can see this as adding a lot of other work when needing to access specific information about the tasks of an invoice, but not being too much work.

Another idea was to add a column after Date of Billing that simply held a JobID. Then have a new Job Table that stored all the jobs and a matching JobID. Example:

Invoice Table
| DateOfBilling | JobID
Job Table
| JobID | Date | ServiceDescription | Hours | Cost |

This seems cleaner but I'm not experienced enough to know what kind of hit this would be on performance if every time the job list is needed for an invoice I have to query a table of every job over a span of time for 1-5 or so entries.

Thanks ahead for any help you can provide.

• outis: What type of association is there between invoices and jobs: many-to-many, one-to-many?

Answer 1

Assuming you also have a fixed list of possible Job descriptions, you might go with:

A Jobs reference table:
JobId, Description, BaseTimeEstimate ...
Invoice table:
InvoiceId, DepartmentId, DateOfBilling ...
Then an InvoiceDetail table:
InvoiceId, JobId, ActualHours, Cost ...

something like that.

For Jobs that could be custom entries, a simpler structure works:

Invoice table:
InvoiceId, DepartmentId, DateOfBilling ...
Then an InvoiceDetail table:
InvoiceId, JobId, JobDescription, ActualHours, Cost ...

like that.

• Caleb S: Some of the jobs could be fixed, but a lot of them would be custom entries.