I am confused as to whether I should use numeric IDs or not

Hi,

I have been struggling with this question ever since taking the data design class. In the class, we are taught that the primary key should be unique and should serve as the foreign key in other tables if necessary. Fine.

Now I have a real life situation. I am working in a database problem which has to keep track of customers, salespeople, and the contact history between the two. So I have created 3 tables: CUSTOMER, SALESPEOPLE, and CONTACT-HISTORY. I have used a numeric ID as the primary key for each one of them since that is only way to guarantee uniqueness. That being said though, I am confused as to whether and why I should use their NumericIDs in the foreign tables. For example, the CONTACT_HISTORY table should have the id of the contact, date of contact, the name of the salesman, and the name of the customer who the salesman had contact with. So it is CONTACT(ContactID, Date, SalesmanID, CustomerID). What I am wondering is whether I should use the numeric CustomerID or actually the physical name of the customer in the contact table. Because numeric id by itself doesn't tell much. And I have a VB form that would be bound to extract the contact information from my CONTACT table. So if I use numeric ids, every time a contact_history record is accessed, there will be queries made on the CUSTOMER AND SALEMSAN tables as well in order to extract their names based on their numeric IDs. That seems kind of inefficient that you have to run queries on all three tables, when you just want to see the CONTACT records and can easily store the name information in the contact table itself instead of having to store IDs and then running a query back to get the names.

Thanks,
Mike Received on Tue Feb 01 2000 - 00:00:00 CET