Table Setup

[rblair11]

I've thought I had a decent understanding of database normalization but I've come across a fairly simple problem that I'm having difficulty resolving.

I have a database which I use to track submittals from companies.

Originally it was set up as follows:

tblCompany
Company_ID (PK)
Company (No duplicates allowed)

tblSubmittals
Submittals_ID (PK)
Description
Date
Company_ID (FK)

Now I've decided that I want to add a contacts table with fields such as first name, last name, company, etc. My dilemma is that in the Submittals table I need to keep track of which company the submittal is from but I don't care WHO it is from.

So I created another table as follows:

tblContacts
Contact_ID (PK)
Company_ID (FK)
First_Name
Last_Name

This will work except that when I add a new contact I will have to add code to see if the company exists in the tblCompany before I add it.

It doesn't seem like this would be the correct way to set this up because I'm adding the same data to 2 different tables.

I'm sorry if this seems like a stupid question. Thanks for your input.

 

[Pat Hartman]

You haven't said what relationship contacts has with submittals. Is it 1-m or 1-1 or m-m?

 

[rblair11]

It is a 1-m relationship. One company can submit several submittals.

 

[Pat Hartman]

Then if you want to also associate a contact with a submittal, link with the contact_ID. You can derive the company by linking to the contacts table and then to company.

 

[wazz]

Then if you want to also associate a contact with a submittal, link with the contact_ID. You can derive the company by linking to the contacts table and then to company.

if he doesn't want to, he can leave everything the way it is, wouldn't you say?

when I add a new contact I will have to add code to see if the company exists in the tblCompany before I add it.

if you don't make company mandatory in the contact table design it shouldn't be a problem, i think.

 

[rblair11]

Thanks everyone for your imput. I'm keeping it the way I descriped with a table that just holds company names. As I continue to build the app, there will be several occations where I only care about the company.

When it comes to adding a contact, I used a not_in_list event to add new company names to tblCompany if they didn't already exist. I can use the same method now on new forms as well.