MySQL Database Design issue for labor work Invoice

Question

I am designing database for labor work invoice. In that I have following entities.

Labor_invoice_table(This is main table I have created)

Here I am confuse about Job Description and Amount per Job Invoice Line Item.On each Invoice I have multiple Jobs and for each Job its amount.

I search about it on google. Here also I found one useful detail link Found that I should create new Table

Labor_Job_Description

So that multiple job can be stored in another Table.

Is it proper way? Or should I try something else?

Is the job description & amount fixed, meaning same description & amount can be part of multiple invoice? — NMK, Oct 16 '14 at 04:46
No, this job description & amount are only part of this invoice. — Vijay Vankhede, Oct 16 '14 at 04:54

score 1 · Answer 1 · answered Dec 25 '14 at 07:22

To generate a invoice you can use a query like

select ID,LabourName,InvoiceDate,Description,Amount From Invoice Inner join Invoice.ID = Labor_Job_Description.InvoiceID For total amount:

Select sum(amount) from Labor_Job_Description where Labor_Job_Description.InvoiceId= Insert will be something like this..

Insert into Invoice (InvoiceDate,LaborName) values (curdate(),'name');

set @invoiceID= LAST_INSERT_ID();

Insert into Labor_Job_Description (InvoiceNo,Description,Amount) values (@invoiceID,'desc', 10);

score 0 · Accepted Answer · answered Oct 16 '14 at 05:07

You need relation for this.

May be you need many to many relation. Because each labor can have many job or each job can have many labor.

labor

job

labor_job

Now you are ready for join query.

SELECT
 *
FROM
  labor_job AS lg
JOIN
   labor AS l ON lg.labor_id = l.id
JOIN
   job AS j ON lg.job = j.id

2 Answers2