I've done a bit of reading but haven't found anything related. This must be widely discussed, so it's most likely my fault.
I've got a relational database with a number of different tables, each storing their unique "thing". Now I'd like to be able to attach extra information (0 or more) to any entity. A "custom field" if you want.
My first instinct was to have GUID PKs in every table, so that I could reference anything in the database by that GUID. I'd have another table for those extra attachments, where I could attach stuff to those GUIDs, no matter which table they stem from.
I don't want to introduce GUIDs for no good reason. And I fear this isn't one.
An alternative would be to reference the items by "table name" and "PK". This is easy as long as the PK is an IDENTITY INT, but AFAIK forcing a surrogate key is bad practice. Also referencing by "table name" feels wrong.
Am I missing something obvious? Maybe the whole approach of
attach stuff to any item in this db is dubious.
- I'm talking about a single table that stores the extra information belonging to the "main data" tables.
- Let's think of the extra information as a field "comments".
- I would utilize an extra table in order to accomplish a one-to-many relationship. One row main data may have N 'comment' entries.
- The extra information may be attached to any of the "main" entities.
- 'use how often': it's non-essential information. The business data should not rely on it. For me, it's mostly an academic question, but if it turns out to be good design, I could imagine putting it to use.
- 'accuracy': I don't understand - the data should be as safe as the main data.
Here's a different use-case involving a similar principle:
Users should be able to suggest typo corrections to data fields they encounter. I.e. user Sandy thinks a product name is misspelled, so I want her to add a row to a table
corrections. This entry has to reference the original product row as well as the field name. A
correction would reference the table, the exact row by PK and the field she wants changed (and -obviously- a new value for it).
Question still remains: how to reference any field/row for meta-information?