How do you store variables fields ?

Question

Hi,

I've a database where I've comments, votes, galleries, images, etc... For examples, galleries and images can be commented using the same form, then I'll store it in a single table, comment.

I'm wondering how would store this type information in the comment table? Using a ENUM type, a foreign key against a type table, hardcoded on insert, etc.. ?

Answer 1

There are probably a couple of ways you could design this while retaining foreign keys.

The first is to basically have a comment table for each type; keep them separate. For each comment have a foreign key to the parent gallery or image, depending on the table.

The second is to have a table (say commentables) that contains common data for anything that is commentable. Each gallery or image has a foreign key to a unique corresponding row in commentables. Each row in the comment table has a foreign key to the commentable, along with the text of the comment, date, etc.

You'll probably find this useful anyway since you'll eventually want metadata for the collection of comments as a whole...e.g. to specify whether commenting is disabled, enabled for friends only, or enabled for everybody.

Edit Option 3 is like option 1 but instead of having full separate comment tables for each commentable type, just have separate associative tables for each commentable type.

|comments|    |gallery_comments|   |image_comments |
 --------      ----------------     ---------------
|id  (pk)|    |comment_id  (fk)|   |comment_id (fk)|
|text    |    |gallery_id  (fk)|   |image_id   (fk)|
|date    |     ----------------     ---------------
|author  |
 --------

Much better than option 1 in my opinion.

Answer 2

Foreign key against a type table would get my vote. Something like this: