473,699 Members | 2,800 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 2455
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
1652
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 same table. For example, I have a scheme for indicating mappings between dimension records at...
7
1700
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 relationship a new table TreatyBenefit has been
3
3172
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 one occurrance (for example, book titles by an author). But if a field will only have one value,...
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
2539
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 delimiters (e.g. "\r\n" -"\n"). - Bill
20
6920
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 Hookom's Survey Database (thanks Duane!!!), with many modifications added to fit the needs of my lab.
1
2024
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 x---y, all that, and normalization, the real topic is lost in between. Decent explanation or good...
22
2450
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
2883
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, how would I structure my tables for this and keep things normalized? I was thinking perhaps another...
0
8704
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 usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9054
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8936
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8894
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6544
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5879
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4637
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3071
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2015
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.