Database Design Help

[JKarchner]

I am creating a sales database, and i think i may have a problem with what i
would like to accomplish. I have created a table tblSales with fields:
CompanyID, ContactID, MCReportID, Date, Comment. The CompanyID is a foreign
key that refers to tblCompany (the company that purchased the report),
ContactID is a foreign key that refers to tblContacts (the person from the
company that purchased the report), and MCReportID is a foreign key that
refers to tblMCReports (the report that was purchased).

The problem that i think i will have is when a person who purchased a report
leaves his current company for a different one. I would like to retain the
information that that particular person had purchased the report for his
original company. If i were to change the company that contact is associated
with, won't i lose the information saying that he purchased the report for
his original company?

Thank you for all the help you can provide.

 

[Amy Blankenship]

Try tblCompanyContacts where you have
CompanyID
ContactID
ContactDateStart
ContactDateEnd
and any other relevant info to that person at that company, such as phone #

 

[JKarchner]

I am not quite sure that i understand exactly what you are suggesting. I
understand that you want for me to create a new table tblCompanyContacts,
however am i supposed to create this table and keep my other one or create
this one to replace my current table tblContacts. But from what it sounds
like you are asking me to do is create a new record for each contact every
time that person changes companies. If this is the case why couldnt i just
add a field Current to my tblContacts table, where Current is a yes/no field
and it tells whether the person is currently with the company.

If this is what you are suggesting, it was something that i was trying to
avoid, because i dont find it necessary to keep all of the contacts
information with his old company.

I will try to explain a little more on what i was trying to accomplish. I
would like to have a form with a subform on it for to track the sales. The
main form will be based on the Company and so the subform will show all the
sales my company has made to that Company. On this sales subform I would
like for the same data to be displayed as the tblSales (except the CompanyID
should be implied through the child/parent(master) link). Also i would like
for the Contacts to be limited to the ones related to the Company currently
displayed on the form. What you are suggesting would accomplish this, but
also it would leave that Contacts name on the list for further input, when it
should not be allowed.

Thank you again for all of your help and i will be looking forward to
hearing your reponse.

 

[Amy Blankenship]

I am not quite sure that i understand exactly what you are suggesting. I
understand that you want for me to create a new table tblCompanyContacts,
however am i supposed to create this table and keep my other one or create
this one to replace my current table tblContacts. But from what it sounds
like you are asking me to do is create a new record for each contact every
time that person changes companies. If this is the case why couldnt i
just
add a field Current to my tblContacts table, where Current is a yes/no
field
and it tells whether the person is currently with the company.

You are supposed to keep your old one, that would contain a new record for
the contact each time he/she changed company. I thought what you wanted to
do was keep tabs on whether a contact had ordered from you when at the
current or previous companies. If you don't keep a record of all companies
a contact had worked for, how do you think you're going to accomplish that?

If you think a yes/no field is going to provide traceback for you, by all
means use it. However, I don't see that it will give you any information at
all about other companies that might have employed this same person/would
employ him/her in the future.

If this is what you are suggesting, it was something that i was trying to
avoid, because i dont find it necessary to keep all of the contacts
information with his old company.

Well, you don't have to keep the contact information with the old company.
That was just a suggestion in case you needed it. You do, however, need the
rest of the information from the table. If you're not comfortable with the
date range idea, I suppose you could substitute a boolean. But it's always
better to plan to use the more informative solution that will give you the
same effect rather than the less informative one, since you can't go back
and manufacture information that was never entered if you need it, but you
can cut informatioin down fairly easily.

I will try to explain a little more on what i was trying to accomplish. I
would like to have a form with a subform on it for to track the sales.
The
main form will be based on the Company and so the subform will show all
the
sales my company has made to that Company. On this sales subform I would
like for the same data to be displayed as the tblSales (except the
CompanyID
should be implied through the child/parent(master) link). Also i would
like
for the Contacts to be limited to the ones related to the Company
currently
displayed on the form. What you are suggesting would accomplish this, but
also it would leave that Contacts name on the list for further input, when
it
should not be allowed.

What I suggested has nothing to do with form design. That is a completely
separate issue. However, if you know the dates a person worked for a
company, you know whether or not there is an end date for that person or if
there is, if the end date is before today.

-Amy

 

[JKarchner]

Sorry it has taken so long to reply, our servers at work went down and they
came back online yesterday. I think i should describe a little more about my
DB setup, because I think maybe we are taking about the same thing. I have a
table for Contacts listed below:

tblContacts
ContactID
Contact - Contact's full name, i know that standard practice is
separate fields for first and last name, but this will be easier on the end
users.
Salutation
CompanyID - will display company name thru combo box
Title
Department
LocationID - displays location name thru combo box
Phone
Current - whether contact is currently with company
... and so on

I am currently using Current because we are unsure of correct days when a
contact leaves a company. We basically find out when we call and they are no
longer there, and we do not know when they have arrived at a new company
until we hear from them again. I know that what you suggested with the dates
would still work. I would believe this is the type of table that you were
speaking of in your previous replies. We already have "system" in place that
stores some of this information, and i know that we do not have start dates
for any of the contacts. I do know that we could just use the first time we
had spoken with that contact. I hope this makes sense to you. And i believe
that we are on the same path, just using two slightly different ideas.
Either way, i think that i know that i must create a new record every time a
contact leaves a company for another, in order to save that contact's history
with his previous company. Thank you so much.

 

[Amy Blankenship]

Sorry it has taken so long to reply, our servers at work went down and
they
came back online yesterday. I think i should describe a little more about
my
DB setup, because I think maybe we are taking about the same thing. I
have a
table for Contacts listed below:

tblContacts
ContactID
Contact - Contact's full name, i know that standard practice is
separate fields for first and last name, but this will be easier on the
end
users.
Salutation
CompanyID - will display company name thru combo box
Title
Department
LocationID - displays location name thru combo box
Phone
Current - whether contact is currently with company
... and so on

I am currently using Current because we are unsure of correct days when a
contact leaves a company. We basically find out when we call and they are
no
longer there, and we do not know when they have arrived at a new company
until we hear from them again. I know that what you suggested with the
dates
would still work. I would believe this is the type of table that you were
speaking of in your previous replies. We already have "system" in place
that
stores some of this information, and i know that we do not have start
dates
for any of the contacts. I do know that we could just use the first time
we
had spoken with that contact. I hope this makes sense to you. And i
believe
that we are on the same path, just using two slightly different ideas.
Either way, i think that i know that i must create a new record every time
a
contact leaves a company for another, in order to save that contact's
history
with his previous company. Thank you so much.

Your way, there is no way to tell it is the same contact or easily query
his/her history. That person would become a new contact every time. At the
minimum (if you insist on doing it as you've described above), you would
need an OldContactID to connect this contact with his/her previous "self."
However, because that person has NOT become a new person, it is a very bad
data model. Additionally, self joins, which is what you'd have to do in
this case, are difficult for inexperienced users, especially when you'd have
no way of knowing how many times a person might change jobs when writing the
query.

Good luck, though.

 

[JKarchner]

OK so using this design, i should move all of my fields from my current
tblContacts to a new table labed tblCompanyContacts. I should also include
the DateStart and DateEnd fields as you have suggested. Now my original
table would look something like this:

tblContacts
ContactID
Contact
Salutation

Should there be anything else that goes into this table? The only
information we keep on the contact that doesnt partain to the company
information is the Contact's name (Contact) and their Salutation (i.e. Mr,
Mrs, Ms, etc).

Thank you so much for all of your help.

 

[Amy Blankenship]

Unless you wanted to add something that really nails it down to that person.
For instance, there could be a lot of Mr. John Smiths. The usual things you
might use, such as a SSN, wouldn't apply here, but maybe you could have a
notes field "the lady who likes funny hats" or whatever.

HTH;

Amy

 

[JKarchner]

I hope you don't mind me thinking ahead a bit on this... This system i am
creating will be used only by end users and i would like to limit them to the
use of forms only. For them to be able to add a new Contact to the list, i
would need to create a complelety separate form where they would have to
insert the ContactID (if it isnt automated), the Contacts name, and their
Salutation. This is correct isn't it? There wouldnt be any way in avoiding
that is there?

 

[Amy Blankenship]

I wouldn't recommend allowing end users to even SEE the contactID. The
usual is to use AutoNumber.