Data integrity Question

[Russ via AccessMonster.com]

I have a contact table that has the contacts company name, I want to use that
field to link my purchasing and inventory information. Any suggestions and or
comments that may help me ensure data integrity between all information would
be greatly appreciated. I guess my concern is the data may get corrupted by
not having a unique company i.d. and linking to that, can I link to the
company name, or is this not a good idea??? I look forward to any comments
and or suggestions….

Example
FirstName LastName Company
Micheal Smith My Company Name

PartNumber Description DefaultVendor
123456 Widget My Company Name

Acquistion# Date Supplier
999265 01/01/06 My Company Name

 

[Rick Brandt]

I have a contact table that has the contacts company name, I want to use that
field to link my purchasing and inventory information. Any suggestions and or
comments that may help me ensure data integrity between all information would
be greatly appreciated. I guess my concern is the data may get corrupted by
not having a unique company i.d. and linking to that, can I link to the
company name, or is this not a good idea??? I look forward to any comments
and or suggestions..

Example
FirstName LastName Company
Micheal Smith My Company Name

PartNumber Description DefaultVendor
123456 Widget My Company Name

Acquistion# Date Supplier
999265 01/01/06 My Company Name

You should normally link and use as the foreign key in your contacts table
whatever field is the primary key in your companies table. If that is the
company name then fine, but you're correct that this is not a great PK for the
companies table because there could be duplicates. If you have a PK like
CompanyID then that is the field you should be storing in your contacts table
and using for linking between the two.

If there are other fields in your companies table that have unique indexes on
them then those could also be used as the foreign key in the contacts table, but
using the PK is most common.

 

[Russ via AccessMonster.com]

Rick, that is the concern, I do not have a company table, just a company name
field in the contacts table. Hopefully I will not have to create a company
table and could just linf to the company name field in the contacts table.

I have a contact table that has the contacts company name, I want to use that
field to link my purchasing and inventory information. Any suggestions and or

[quoted text clipped - 13 lines]

Acquistion# Date Supplier
999265 01/01/06 My Company Name

You should normally link and use as the foreign key in your contacts table
whatever field is the primary key in your companies table. If that is the
company name then fine, but you're correct that this is not a great PK for the
companies table because there could be duplicates. If you have a PK like
CompanyID then that is the field you should be storing in your contacts table
and using for linking between the two.

If there are other fields in your companies table that have unique indexes on
them then those could also be used as the foreign key in the contacts table, but
using the PK is most common.

 

[Rick Brandt]

Rick, that is the concern, I do not have a company table, just a company name
field in the contacts table. Hopefully I will not have to create a company
table and could just linf to the company name field in the contacts table.

Link to it from where? If you have no companies table then I fail to see what
you would link it to.

Not having a companies table not only has the potential problem where two
companies have the same name, but you also have the problem where the same
company can be entered inconsistently...

Some Big Company
Some Big Co.
Some Big
Some Big Co

 

[Russ via AccessMonster.com]

link from the contacts tables field CompanNname to the products table field
DefaultVendor

 

[Rick Brandt]

link from the contacts tables field CompanNname to the products table field
DefaultVendor

To be blunt...If you have a table for products and a table for contacts then you
really should have a table for companies.

 

[Russ via AccessMonster.com]

I guess my problem is that I am also using a card scanner and import the
information into my contacts table, and think it would be hard to separate
the information from it and add the company name to the companies table then
add the contact information into the contacts table