>You could create a view based on the 2 tables and order it by the vendor id.
>
>HTH
>
>>For those that advocate using surrogate foreign keys (non-business key) I have a question. How do you best handle sorting on a foreign key? For example, I have an Ap invoice table with a foreign key to my vendor table (unique integer). I don't have any way to SET ORDER to my vendor field to easily sort my invoices by vendor id. I could bag the integer foreign key in the Invoice table and replace it with the vendor id itself or I could include both the integer key value and the actual vendor id in my invoice table. I like using the integer for speed, ease of use, etc. and although it violates normalization, it may be helpful to have both for sorting. Any suggestions?

I've had hard time picking the best message in this thread to use as a starting point. I should have started a new thread instead, but I'd need lots of explaining to start with, so be it here.

It's the fields like CustID, LineItemID and other ID fields. What do we need them for? IMO, we need them just because the users are using them already. Post office is using ZIP codes, publishers are using ISBN numbers, there are SSN numbers etc etc.

I've seen in several messages in various threads (including this one) an intent to hide the complexity of using these IDs, move them into surrogate (P & F) keys but - IMO, we still need visible business keys. Relying on item descriptions is something I've burned my fingers on too many times, and forcing the use of IDs is my preferrable way of keeping the data tidy. I've even seen a book title, consisting of two words printed in (friendly :) large letters, being retyped six different ways into the items table, and all of the six ways were wrong ways. Also, having all the possible combinations of quotes, commas instead of quotes etc etc in customer names makes even search grids less usable.

I'll give the user all the possible help, including incremental search grids and other bells 'n' whistles, to find the proper ID, but I don't intend to hide the ID in most of the cases (exceptions are a few small lookup tables with up to 20-30 records, with automatic IDs and no business keys).

So, the question i(d)s: business keys, use them or not, hide them or not. Ladies and gentlemen, I'm waiting for a wise word on the subject.