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

Hi!

You have to use the numeric ID as foreign key. Since you state yourself that only this way you can guarantee uniqueness. If a name occurs twice, you would never be able to tell which CUSTOMER was contacted.

In relational databases you collect information using queries on different tables. If you absolutely don't want to join for your VB form, you could store the names in the CONTACT table, too. That way you break normalisation and can run into troubles, but you have the data at hand. I'd suggest that solution only, if it the query is really time critical or the form can't be filled another way.

Regards,
Heinz

Mike Phillips wrote:
>
> 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