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 7 2478
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 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
"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? 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
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.
> 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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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...
|
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
|
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,...
|
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
|
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
| |
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.
|
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...
|
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
|
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...
|
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...
|
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
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.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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,...
|
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...
|
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 launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |