Hi all, I need a bit of help sketching out a very simple relationship graph that contains 6 tables.

Due to redundancy, I'm building a revised solution for my friend's moving company and these are the main tables:

CUSTOMERS, JOBS (ORDERS), SERVICES, INVOICES, PAYMENTS, ORDER LINE

The solution should allow my friend to book a job for a customer, print an invoice and later apply a payment to that invoice once the service has been rendered.

I'd appreciate seeing a few ways of achieving this as I already have a good base, but I'm still lacking on a few points. I added the ORDER LINE table to solve the many-to-many issue that I would have had if I linked JOBS to SERVICES.

Any help will be appreciated.

How are Jobs and Invoices connected?

Hey crazy eye,

Thanks for the time.

This is one area where I'm stuck .

Since a job can sometimes have many invoices (ex: a follow-up job), but an invoice can only be attached to 1 job at a time, we should have a 1-to-many relationship where the JOB becomes the parent.

For further clarifications here's how my "ERD" looks like:

CUSTOMER(Customer_ID) -< JOBS (Customer_ID_fk)

JOBS (Job_ID)-< ORDER LINE (Job_ID_fk)

PRODUCTS(Product_ID) -< ORDER LINE (Product_ID_fk)

INVOICE(Invoice_ID) -< JOBS(Invoice_ID-fk)

PAYMENTS(Payment_ID) -< INVOICES(Payment_ID-fk)

-< means 1-to-many

As you can see, I need some extra help to sort this out. How should link payments and invoices etc.

Edited August 19, 200916 yr by Guest

Looks fine. Where are you stuck. The only issue I see is a customer that sends a payment for more than one invoice or not enough for an invoice You could use a join table for Invoices and Payments.