473,560 Members | 2,937 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Normalization Question

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

Nov 13 '05 #1
7 2445
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:dd******** *@enews1.newsgu y.com...
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

Nov 13 '05 #2
You should be careful when
designing order queries, that they don't depend on the Company - contact
relationship as this may change
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" <br****@gofree. indigo.ie> wrote in message
news:dd******** **@reader01.new s.esat.net... 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:dd******** *@enews1.newsgu y.com...
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


Nov 13 '05 #3
"John Welch" <j+ohnw+elch@ca l+central.com (remove +'s)> wrote in message
news:dd******** *@enews1.newsgu y.com...
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


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?
Nov 13 '05 #4
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?
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:dd******** **@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com... "John Welch" <j+ohnw+elch@ca l+central.com (remove +'s)> wrote in message
news:dd******** *@enews1.newsgu y.com...
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


Nov 13 '05 #5
rkc
John Welch wrote:
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.


So don't change the current CompanyContacts record. Make ContactID and
CompanyID the primary key and add a new record.
Nov 13 '05 #6
> 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.


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.
Nov 13 '05 #7
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:
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


Nov 13 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
1645
by: Steve Jorgensen | last post by:
I've come up with this issue in several apps now. There are things that, from one perspective, are all handled the same, so it would be desirable that they all be handled in the same table with some field as a type specification. From other perspective of foreign key relationships, however, they are different things and can't be stored in the...
7
1691
by: Adrian | last post by:
Normalization Question - Please bear with me, hopefully things will be clear at the end of the question. Given a treaty table containg treaties; Treaty1, Treaty2 etc and a benefit table; Benefit1, Benefit2 etc. A treaty can only have certain benefits:- For example Treaty 1 can process Benefit1 and Benefit2. To maintain this...
3
3161
by: JoeB | last post by:
I found an interesting thread on this from Nov., 2000, but it didn't fully answer my question. I understand normalization, but am trying to find the line between good database design and personal preference. I'm wondering if I've created too many tables. Normalization as I understand it would be to create tables if a field can have more than...
1
407
by: Tom | last post by:
Hi: I have a question regarding normalization of a database. I am trying to model the inspection and repair of items. Also need to track when they go in service, out of service and several other actions relating to the item. My first shot has the following structure: tblItem · ItemID · ItemName
4
2531
by: William Ahern | last post by:
Has it ever been proposed or posited within any C committee to define or discuss (in a standard's document) the transformation of Unicode text I/O according to a Unicode Normalization Form (assuming a locale which employs a Unicode representation)? Is such a capability implicit? The notion exists and seems to work well for line/record...
20
6894
by: hippomedon | last post by:
Hello everyone, I'm looking for some advice on whether I should break the normalization rule. Normally, I would not consider it, but this seems to be a special case. I have created an "Outcomes Database" used to store response data from measures/ questionnaires for a longitudinal health study. It is essentially derived from Duane...
1
2017
by: weird0 | last post by:
I know that is not the appropriate group for asking this question, but I do not know of any better forum. Can anyone plz explain clearly what normalization is? The first three normal forms upto 3NF and BCNF. I know the first one but the remaining ones are really confusing. The language changes dramatically into functional dependencies...
22
2439
by: sophia | last post by:
Dear all, can any one explain what is meant by pointer normalization given here:- http://c-faq.com/ansi/norml.html
4
2871
by: FooFighter | last post by:
I was going to make a database to store a list of my DVD's. I have a question about the table structure though. I want to have some fields for actors. I'm thinking 4 or 5 would be plenty. From what I understand it wouldn't be proper to have fields like Actor1 Actor2 and so on. As I would want to be able to search for a movie based on an actor,...
0
7632
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7552
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7841
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8066
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7920
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6191
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5458
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3585
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1169
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.