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.
