Foreign Keys

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Hi all,

A pretty amateur quesiton, but: Say I have two tables - Parts and
Suppliers. I want to link each Part to a Supplier. Is it better to use a
numeric SupplierID as a foreign key in the Parts table, or to use the
Supplier's Name instead as a foreign key instead?

My concern is in listing info from the Parts table - I don't want to a bunch
of meaningless numbers to be shown for the Supplier of each part.

Thanks,

Dave Hagedorn
 
Use a numeric identifier. When you create your query, join on the keys, but
choose the supplier name to display. Base your forms and reports on this
query.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/Newsgroups
 
My advice is to use the numeric Supplier ID - that way if the name of the
supplier changes you can still maintain the links. I think that it is always
a good idea to use an internal auto generated number as the link so as to
maintain data integrity. Otherwise if someone goes into a table and changes
some information you can lose all of the links. For instance if Microsoft
was recorded as the supplier in the supplier file and its name changed in
the supplier file. It would not change in the parts file and therefore the
links would be broken.

In the parts table make the SupplierID a lookup table with the bound column
as 1, the column count as 2 and the column width as 0;5cm(to give you enough
space to display the supplier name. The rowsource should be a query that
sorts the suppliers alphabetically.

Regards,

Michael McCann
 
Hi all,

A pretty amateur quesiton, but: Say I have two tables - Parts and
Suppliers. I want to link each Part to a Supplier. Is it better to use a
numeric SupplierID as a foreign key in the Parts table, or to use the
Supplier's Name instead as a foreign key instead?
Click to expand...

Generally the ID. Names are long; they can change; and (most
importantly) they aren't guaranteed to be unique - there must be
scores of companies calling themselves "Quality Supplies" or the like.
My concern is in listing info from the Parts table - I don't want to a bunch
of meaningless numbers to be shown for the Supplier of each part.
Click to expand...

That's only an issue if you use the table datasheet for a user
interface. So don't! Users should essentially never even SEE a table
datasheet; for onscreen use they should use Forms, for printing
Reports. And on these you can use the tools Access provides, such as
queries and combo boxes, to have the ID stored but invisible, and the
name displayed.
 
Back
Top