Normalization Question

[John Welch]

I have three tables and the way they are currently set up violates good
normalization, but I'm having trouble figuring out a better way. Can someone
suggest a more elegant solution?

My tables are:

tblCompanies:
CompanyID - PK
other fields like address, type of business, etc

tblCompanyConta cts:
ContactID- PK
CompanyID - linked to companies (many contacts per company)
other fields like phone, etc

tblJobOrders:
JobOrderID -PK
ContactID - linked to tblCompanyConta cts (many Job orders per Contact)
CompanyID - linked to Companies
other fields containing job order info

The bad normalization happens in the JobOrders table, where I store
both companycontact and company. I originally had only ContactID
in JobOrders, and a JobOrder would know its company by looking at the
company the contact worked for. But the problem bugging me was that if a
Contact person changed companies, the Job order would then point at their
new company, when it should still point to the original company. So I
decided to keep both CompanyID and ContactID fields in JobOrders. In the
form, they will first choose a company and then select from that company's
contact list. If a contact moves companies, the contact field will be
outdated, but at least the company will be correct.
I think there must be a better way ( I hate to compromise on normalization),
but I can't think of it. Any ideas?

thanks in advance
-John

[John]

Re: Normalization Question

The contact ID is going onto the orders table, so unless you need to know
when a contact starts or finishes with a particular company then it doesn't
matter. The details of the contact remain in the contact table, so the
contact ID in each order record is still valid even if the contact no longer
works for that company. The only thing you will change when a person moves
is the Company ID in the contacts table. You should be careful when
designing order queries, that they don't depend on the Company - contact
relationship as this may change

Hope this helps

John


"John Welch" <j+ohnw+elch@ca l+central.com (remove +'s)> wrote in message
news:ddqmc601pb q@enews1.newsgu y.com...[color=blue]
>I have three tables and the way they are currently set up violates good
> normalization, but I'm having trouble figuring out a better way. Can
> someone suggest a more elegant solution?
>
> My tables are:
>
> tblCompanies:
> CompanyID - PK
> other fields like address, type of business, etc
>
> tblCompanyConta cts:
> ContactID- PK
> CompanyID - linked to companies (many contacts per company)
> other fields like phone, etc
>
> tblJobOrders:
> JobOrderID -PK
> ContactID - linked to tblCompanyConta cts (many Job orders per Contact)
> CompanyID - linked to Companies
> other fields containing job order info
>
> The bad normalization happens in the JobOrders table, where I store
> both companycontact and company. I originally had only ContactID
> in JobOrders, and a JobOrder would know its company by looking at the
> company the contact worked for. But the problem bugging me was that if a
> Contact person changed companies, the Job order would then point at their
> new company, when it should still point to the original company. So I
> decided to keep both CompanyID and ContactID fields in JobOrders. In the
> form, they will first choose a company and then select from that company's
> contact list. If a contact moves companies, the contact field will be
> outdated, but at least the company will be correct.
> I think there must be a better way ( I hate to compromise on
> normalization), but I can't think of it. Any ideas?
>
> thanks in advance
> -John
>
>
>[/color]

[John Welch]

Re: Normalization Question

[color=blue]
> You should be careful when
> designing order queries, that they don't depend on the Company - contact
> relationship as this may change[/color]

right, that's the problem: I will want to see what company a Job Order came
from. If I just change the companyID for the contact, I will lose the
information of what company created the job order.


"John" <bravo6@gofree. indigo.ie> wrote in message
news:ddr23f$3kr $1@reader01.new s.esat.net...[color=blue]
> The contact ID is going onto the orders table, so unless you need to know
> when a contact starts or finishes with a particular company then it
> doesn't matter. The details of the contact remain in the contact table, so
> the contact ID in each order record is still valid even if the contact no
> longer works for that company. The only thing you will change when a
> person moves is the Company ID in the contacts table. You should be
> careful when designing order queries, that they don't depend on the
> Company - contact relationship as this may change
>
> Hope this helps
>
> John
>
>
> "John Welch" <j+ohnw+elch@ca l+central.com (remove +'s)> wrote in message
> news:ddqmc601pb q@enews1.newsgu y.com...[color=green]
>>I have three tables and the way they are currently set up violates good
>> normalization, but I'm having trouble figuring out a better way. Can
>> someone suggest a more elegant solution?
>>
>> My tables are:
>>
>> tblCompanies:
>> CompanyID - PK
>> other fields like address, type of business, etc
>>
>> tblCompanyConta cts:
>> ContactID- PK
>> CompanyID - linked to companies (many contacts per company)
>> other fields like phone, etc
>>
>> tblJobOrders:
>> JobOrderID -PK
>> ContactID - linked to tblCompanyConta cts (many Job orders per Contact)
>> CompanyID - linked to Companies
>> other fields containing job order info
>>
>> The bad normalization happens in the JobOrders table, where I store
>> both companycontact and company. I originally had only ContactID
>> in JobOrders, and a JobOrder would know its company by looking at the
>> company the contact worked for. But the problem bugging me was that if a
>> Contact person changed companies, the Job order would then point at their
>> new company, when it should still point to the original company. So I
>> decided to keep both CompanyID and ContactID fields in JobOrders. In the
>> form, they will first choose a company and then select from that
>> company's contact list. If a contact moves companies, the contact field
>> will be outdated, but at least the company will be correct.
>> I think there must be a better way ( I hate to compromise on
>> normalization), but I can't think of it. Any ideas?
>>
>> thanks in advance
>> -John
>>
>>
>>[/color]
>
>[/color]

[Justin Hoffman]

Re: Normalization Question

"John Welch" <j+ohnw+elch@ca l+central.com (remove +'s)> wrote in message
news:ddqmc601pb q@enews1.newsgu y.com...[color=blue]
>I have three tables and the way they are currently set up violates good
> normalization, but I'm having trouble figuring out a better way. Can
> someone suggest a more elegant solution?
>
> My tables are:
>
> tblCompanies:
> CompanyID - PK
> other fields like address, type of business, etc
>
> tblCompanyConta cts:
> ContactID- PK
> CompanyID - linked to companies (many contacts per company)
> other fields like phone, etc
>
> tblJobOrders:
> JobOrderID -PK
> ContactID - linked to tblCompanyConta cts (many Job orders per Contact)
> CompanyID - linked to Companies
> other fields containing job order info
>
> The bad normalization happens in the JobOrders table, where I store
> both companycontact and company. I originally had only ContactID
> in JobOrders, and a JobOrder would know its company by looking at the
> company the contact worked for. But the problem bugging me was that if a
> Contact person changed companies, the Job order would then point at their
> new company, when it should still point to the original company. So I
> decided to keep both CompanyID and ContactID fields in JobOrders. In the
> form, they will first choose a company and then select from that company's
> contact list. If a contact moves companies, the contact field will be
> outdated, but at least the company will be correct.
> I think there must be a better way ( I hate to compromise on
> normalization), but I can't think of it. Any ideas?
>
> thanks in advance
> -John[/color]

Your proposed solution is definitely a possibility but it is difficult to
say whether it is the best. Even if you put this in place, it might not
represent the full picture.
What if the contact's name was Lisa Jones when you did the job, but after
getting married, is now Lisa Smith? You lose this info but does anyone
care? Probably not - but only you can say. For all we know, the best
solution might be to have a simple text field tblJob.Contact and that way
you keep that field is kept 'as is' at the time of the job.
Probably, though, your current solution would be better, but have you
thought what happens if a company changes its name for example? What does
the job history look like then?

[John Welch]

Re: Normalization Question

[color=blue]
> What if the contact's name was Lisa Jones when you did the job, but after
> getting married, is now Lisa Smith? You lose this info but does anyone
> care? Probably not - but only you can say. For all we know, the best
> solution might be to have a simple text field tblJob.Contact and that way
> you keep that field is kept 'as is' at the time of the job.
> Probably, though, your current solution would be better, but have you
> thought what happens if a company changes its name for example? What does
> the job history look like then?[/color]

Thanks Justin-
I'm not interested in history, but just the company and contact that created
a particular job order. No problem if their names change, since they will be
identified by their ID's.

"Justin Hoffman" <j@b.com> wrote in message
news:ddrah2$51t $1@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com...[color=blue]
> "John Welch" <j+ohnw+elch@ca l+central.com (remove +'s)> wrote in message
> news:ddqmc601pb q@enews1.newsgu y.com...[color=green]
>>I have three tables and the way they are currently set up violates good
>> normalization, but I'm having trouble figuring out a better way. Can
>> someone suggest a more elegant solution?
>>
>> My tables are:
>>
>> tblCompanies:
>> CompanyID - PK
>> other fields like address, type of business, etc
>>
>> tblCompanyConta cts:
>> ContactID- PK
>> CompanyID - linked to companies (many contacts per company)
>> other fields like phone, etc
>>
>> tblJobOrders:
>> JobOrderID -PK
>> ContactID - linked to tblCompanyConta cts (many Job orders per Contact)
>> CompanyID - linked to Companies
>> other fields containing job order info
>>
>> The bad normalization happens in the JobOrders table, where I store
>> both companycontact and company. I originally had only ContactID
>> in JobOrders, and a JobOrder would know its company by looking at the
>> company the contact worked for. But the problem bugging me was that if a
>> Contact person changed companies, the Job order would then point at their
>> new company, when it should still point to the original company. So I
>> decided to keep both CompanyID and ContactID fields in JobOrders. In the
>> form, they will first choose a company and then select from that
>> company's contact list. If a contact moves companies, the contact field
>> will be outdated, but at least the company will be correct.
>> I think there must be a better way ( I hate to compromise on
>> normalization), but I can't think of it. Any ideas?
>>
>> thanks in advance
>> -John[/color]
>
>
>[/color]

[rkc]

Re: Normalization Question

John Welch wrote:[color=blue]
> I have three tables and the way they are currently set up violates good
> normalization, but I'm having trouble figuring out a better way. Can someone
> suggest a more elegant solution?
>
> My tables are:
>
> tblCompanies:
> CompanyID - PK
> other fields like address, type of business, etc
>
> tblCompanyConta cts:
> ContactID- PK
> CompanyID - linked to companies (many contacts per company)
> other fields like phone, etc
>
> tblJobOrders:
> JobOrderID -PK
> ContactID - linked to tblCompanyConta cts (many Job orders per Contact)
> CompanyID - linked to Companies
> other fields containing job order info
>
> The bad normalization happens in the JobOrders table, where I store
> both companycontact and company. I originally had only ContactID
> in JobOrders, and a JobOrder would know its company by looking at the
> company the contact worked for. But the problem bugging me was that if a
> Contact person changed companies, the Job order would then point at their
> new company, when it should still point to the original company. So I
> decided to keep both CompanyID and ContactID fields in JobOrders.[/color]

So don't change the current CompanyContacts record. Make ContactID and
CompanyID the primary key and add a new record.

[Mondrogan]

Re: Normalization Question

> right, that's the problem: I will want to see what company a Job Order[color=blue]
> came from. If I just change the companyID for the contact, I will lose the
> information of what company created the job order.[/color]

Not so. In tblCompanyConta cts, the Company ID refers to the company at which
that contact currently works. In the Job Orders table, the Company ID field
refers to the company with which the order was placed. If a contact leaves,
the Company ID field in CompanyContacts will change, but the Company ID in
JobOrders stays the same (Contact ID in JobOrders changes). Bear in mind
that the Job Order belongs to the Company, not to the Contact.

[lesperancer@natpro.com]

Re: Normalization Question

so on the jobOrders table, companyId is the company that requested the
work
and the contactId, is the contact with that company who requested the
work

the contact's company may change from IBM to Microsoft and that's fine,
you just need a query to identify job contacts who are no longer
employed by the job's company, so that you can update the job's
information

your current design also lets you deal with subcontractors, John Smith
works for IBM and has requested that you do a 'job' for Microsoft

I think your design of the jobOrders table is fine as is, unless a job
can have multiple contacts, in which case, you'd need a
jobOrdersContac t table

John Welch (remove +'s) wrote:[color=blue]
> I have three tables and the way they are currently set up violates good
> normalization, but I'm having trouble figuring out a better way. Can someone
> suggest a more elegant solution?
>
> My tables are:
>
> tblCompanies:
> CompanyID - PK
> other fields like address, type of business, etc
>
> tblCompanyConta cts:
> ContactID- PK
> CompanyID - linked to companies (many contacts per company)
> other fields like phone, etc
>
> tblJobOrders:
> JobOrderID -PK
> ContactID - linked to tblCompanyConta cts (many Job orders per Contact)
> CompanyID - linked to Companies
> other fields containing job order info
>
> The bad normalization happens in the JobOrders table, where I store
> both companycontact and company. I originally had only ContactID
> in JobOrders, and a JobOrder would know its company by looking at the
> company the contact worked for. But the problem bugging me was that if a
> Contact person changed companies, the Job order would then point at their
> new company, when it should still point to the original company. So I
> decided to keep both CompanyID and ContactID fields in JobOrders. In the
> form, they will first choose a company and then select from that company's
> contact list. If a contact moves companies, the contact field will be
> outdated, but at least the company will be correct.
> I think there must be a better way ( I hate to compromise on normalization),
> but I can't think of it. Any ideas?
>
> thanks in advance
> -John[/color]