> Usually the recommended practice in Cayenne is not to use primary keys > with user-meaningful values.
The main counterargument is that you'll find that users will see the ID numbers and promply start using them. Hereabout, we're saying "9" instead of "Neuenhagen". It's shorter, and we're not interested in the location, but only in the articles in stock there (trade company here, so it's articles and prices we care about, names are exchangable). The real distinction is mutability. If you're sure that some value will never change, and it's always available, you can use it as a PK. Invoice numbers are pretty immutable, for example (nobody wants to explains discrepancies of that kind to the IRS). An EAN to identify an article might be less advisable, articles from another continent might not have one. Or your supplier might correct a faulty EAN they sent you earlier. > In our shop we've learned over the years > to add a simple integer PK to join tables... simpler. Depends. Our bread-and-butter table is STOCK: number of articles in stock at a given location. It's PK combines from location and article ID. Why would we need another STOCK_ID field for that? It needs to have LOCATION_ID and ARTICLE_ID as foreign-key fields already. Okay, there's the possibility of subdetail tables. E.g. We have HISTORY, listing historic STOCK data by day. True, HISTORY would be easier to join with STOCK if HISTORY could use STOCK_ID. On the other hand, writing a query that filters or groups HISTORY by article or location (and we do that often!) would require an extra join. The advantage in simplified SQL dissolves, plus the database has to work harder. Now I'm curious: How are integer PKs making life simpler for your shop, even in the light of this complication?
