I am having problems with understanding how to have one address table for my customers, that relate to the customers jobs, their invoices and quotes etc... I can have 1 customers with many jobs related to one address, then 1 customer with many address and many jobs. I've been unable this far to work out how to get this to work with out running into duplicating the same address in the address table.

I've tried to make work a junction table to work between the customers, jobs, address, quotes and invoices but end up confused. Even going through this relationship seminar twice I'm unable to see how the examples relate to what I'm trying to achieve.

Can anyone help?

---

Reply from Richard Rost:

I would have 3 tables: customers, addresses, jobs.

AddressT has CustomerID as a foreign key
JobT also has CustomerID as a foreign key

Now, assuming a JOB will have ONE AND ONLY ONE address, you could just have one AddressID field as a foreign key field in your JobT table.

If a job can have MULTIPLE addresses, then you need to set up a FOURTH table as a junction table which would look like:

AddressesForJobsT
ID: AutoNumber
JobID: Foreign Key to JobT
AddressID: Foreign Key to AddressT
Details...