G
@lpope187 said:This is one of those gray areas between theory and practice. Most definitely I would not have the job reference any address associated with customer directly, I'd copy the address entry from the customer and associate it with the job directly. There are several reasons I'd do this. One is for drop shipments - I buy something but I want it shipped to my sister (a new address entry associated only with that order). Another reason is location moves - either a company changes their headquarters or gets purchased and absorbed. You need the original shipoing address for tax and auditing purposes, but any new orders go to the updated address on the customer master. The choice of adding those actual address fields to the job somewhat eliminates the accidental rewrite of shipping history by updating the master record - so in this case having the address fields directly on the job might be appropriate.
no reason that you would need to replicate the fields in different tables, we could do something like...
customer {customer#, name...}
pk {customer#}
address {address#, postcode, addressline1....}
pk {address#}
customer_address {customer#, address#, address_type, active}
pk {address#, customer#, address_type}
fk {customer# customer.customer#, address# address.address#};
job {job#, customer#, location#, start_date, estimated_end_date}
pk {job#}
fk {customer# customer.customer#, location# customer_address#}
job_completed {job#, actual_end_date, completed_by, notes}
pk {job#}
fk {job# job.job#}
type address_type {main, secondary, shipping, satelite}
customer_data {customer#, name, value}
pk {customer#, name}
fk {customer# customer.customer#}
This would preserve customer address history (all addresses and all historic addresses), and would allow us to compose the following queries. all jobs and the address of the job, all customers at a certain address, all jobs at a certain address, all jobs for a customer at a certain address.