> > > Natural Keys have a purpose but when do they exist in the database? > > In my case it's the tags. Table tags has only two columns id and tag. Each one has a unique index. I have many other similar lookup tables. For example things like order_status, item_type etc.
> What about the Natural Keys of an external source? Should that be stored > in a lookup table along with the integer based Surrogate Key? > Maybe I'll call it "bars_pks". > > I always keep those. The API's I expose to clients and partners allow them to modify the records they send and I let them refer to items by their own primary keys. This has always worked out well for me. > > --Spelling error. > > UPDATE tags SET tag = 'foo' WHERE tag = 'fu'; > > This will fail unless you ON UPDATE CASCADE. > > Yes of course you'd need the on update cascade. Then again maybe I don't even need that tags table. Tags could just be a view (select distinct tag from child_tags). I am not saying that's efficient or desirable but it's possible.
