I typically create an Identity ID column for the PKs of all my tables. My thinking is I have full control over changing the table columns and it seems cleaner especially when using foreign keys.
I was thinking about a downside to this approach. Consider a DB which tracks a number of businesses, each with their own product categories and products. Below is how I would typically deal with this:
The problem I see is it is it is possible to join products with product categories of a different business unit:
I can fix it by making the ProductCategory PK BusinessId and Name fields. Then the Product table's BusinessId must match. This enforces integrity but seems ugly to me. Is there a better way or do I just need to let go of my habit of using identities for all PKs?