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

From: Tony Perovic <tperovic_at_mediaone.net>
Date: 2000/02/01
Message-ID: <pbCl4.27569$L4.49850818_at_rmnws01.ce.mediaone.net>

Keep the tables normalized and use views to pull together the data you need for VB using views like this:

SELECT Contact-History.*

       Customers.Customer name, 
       Contacts.Contact name, 
       SalesPeople.Salesperson name, 
  FROM Contact-History 
       INNER JOIN Contacts ON Contact-History.ContactID = 
Contacts.Contactid 
       INNER JOIN Customers ON Contact-History.CustomerID = 
Customers.Contactid 
       INNER JOIN Salespeople ON Contact-History.SalesPersonID = 

Contacts.SalesPersonID
  WHERE ...
  ORDER BY ... Mike Phillips <mphil_at_matrixone.com> wrote in message news:W3wl4.54985$zU5.751457_at_wbnws01.ne.mediaone.net...
> 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