Creating a Database

[John Cassell]

Hi all.

I am at the planning stages of a database to replace our current system and I have a question about how to design it and normalization.

In our current system, we have a customer code A000 and the name of the customer is ABC Ltd.

In the TBL_Jobs, it has two columns (JobID & CustCode) with the following values 12345, A000.

When querying the job it will load the JobNo 12345 and the custcode A000. I also want to know what the custname is so it will show ABC Ltd because it has checked the TBL_Customers to see what the name is.

This is fine but raises a particular problem.. What if, in 5 years, ABC Ltd change their name to XYZ Ltd. If I change the name then all jobs for the last 5 years will read XYZ Ltd when they are queried!

I thought about having a column in the TBL_Jobs called CustName which checks the TBL_Customers and then just prints ABC Ltd into that field but from what I have learnt about normalization, this is a no-no because you are repeating information.

Could someone point me in the right direction pls.

Many thanks

John

 

[cjard]

you use datetime fields to specify when records are effective from and to (in case of contact details) and each job should have a time that it starts, right?

jobs JOIN (middleman table) JOIN customers
ON jobcustid = custcustid AND jobstarttime BETWEEN custeffectivefrom AND custeffectiveto

note that customer table now has to have a compound primary key of custid+effectivefromdate