Need Help skecthing out a Relationship graph

[Treigh]

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.

[David Jondreau]

How are Jobs and Invoices connected?

[Treigh]

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, 2009 by Guest

[David Jondreau]

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.