Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

EDIT:

  • 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.

EDIT2:

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?

share|improve this question

closed as unclear what you're asking by RolandoMySQLDBA, Kin, Max Vernon, Shawn Melton, swasheck Dec 16 '13 at 2:39

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Are you trying to stuff all this extra information into the same table or do you envision 1 stuff table per physical table? Why do you think there is value in storing axillary data not in the same table as the main data itself? How often do you expect to use this? How crucial is it that the data in the extra table is accurate? –  billinkc Dec 8 '13 at 20:18
    
@billinkc: I updated my original question. –  snwflk Dec 8 '13 at 23:07
add comment

2 Answers 2

Suggestions

My first suggestion is that the business database is not the right place to store comments on what it contains.

Keep the comments in your issue tracker instead. A good issue tracker provides all the features to meet your requirements.

If you still want to do this in the database, research the history table pattern. See this Stack Overflow question for design ideas.

Essentially, you'd have an extra table for every table that you want to store comments about. Each table would refer to the business data by primary key, and would store the name, date, revision of the comment as well.

I've stated my assumptions below. Please correct me so we can help you better.

Assumptions

You have an issue tracker. It's regularly backed up and you can restore from backup.

You want to store instructive or descriptive text for some of the business data.

You would distinguish multiple comments by date of entry and by order of entry.

Most of the data would have no comments.

You want to be able to search the comments. Comment search queries run seldom compared to business queries.

You want to keep comments on deleted data.

You don't need to programatically cross-reference the business data and the comments or analyze them together. You just need to view and edit complete comments.

The comments do actually have value to the business (You are part of the business, right?).

Greater comment visibility would benefit the business.

Your business would react badly if the comments were lost.

share|improve this answer
add comment

Guid. RowIdentifier is a known standard concept and also used for replication. Do not make it the primary key - it is a unique indexed additional field.

THen obviously you need triggers / cascading deletes to clean up the additional data.

I would be carefull with that, but it is a doable approach. Perormance may stink - but that will always be the case whne you go to a totally "any table any data" approach.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.