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

Mike Phillips schrieb in Nachricht ...
>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.

yes ! one reason is the definition of a 'foreign key' - it has to be a primary key in the referenced table - other reasons are all the thoughts about normalization in the relational theory (3rd NF)

>So it is CONTACT-HISTORY(ContactID, Date, SalesmanID, CustomerID).

be aware, that SalesmanID || CustomerID || Date is a synonym for ContactID ...
but don't woory about that now ...

>I should use the numeric CustomerID

definitely yes !!

>Because numeric id by itself doesn't tell much.

that does not matter !

>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 SALESMAN tables as well in order to extract their
>names based on their numeric IDs.

correct ... thats what the 3rd NF does to us - and in the end its more efficient and redundancy free (!) than any other way of doing it - BTW: it wouldn't be any issue with Oracle Forms ... thats designed around these features ...
on the serverside, it's highly probable, that all the information you want to look up are in memory anyway ...

>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.

once you imagine - you've got a big customer, a lot of orders and he changes his name ...

hth Received on Wed Feb 02 2000 - 00:00:00 CET