SQL how to reference a variable column

Question

I'm trying to create a wallpaper sharing gallery, and i want my visitors to be able to post comments on both wallpapers and articles.

Let's say i have the following tables :

Wallpaper
- id
- name
- etc...

Article
- id
- title
- etc...

Comment
- id
- text

I would like a comment to be associated to either a unique wallpaper or a unique article.

I've thought about making inheritance like this :

Commentable
- id

Article
- id references Commentable.id
- etc...

Wallpaper
- id references Commentable.id
- etc...

Comment
- id
- commented references commentable.id

But this method looks heavy and not really nice.

I've also thought about making :

Wallpaper
- id
- name
- etc...

Article
- id
- title
- etc...

Comment
- id
- commented references either Wallpaper.id or Article.id

But there would be no way to know if the associated thing is a wallpaper or an article.

Also i want to use MyISAM and not InnoDB as i'll upload this on a free web host service with a 5.1 MySQL.

Do you know any clear way of doing this please ? Thanks in advance.

EDIT: Thanks to everyone for your answers.

Answer 1

I would recommend adding a type field to your Comment table. You could populate it with a string like 'ARTICLE' or 'WALLPAPER', or create a ref table. Something like:

Type_Ref
- id
- desc

Type_Ref would contain the auto incremented primary key, and a description of Wallpaper or Article.

Then in your comment table, you would just have a Type_Ref_Id field, with the corresponding id. Then, whenever you need to add a new Type to vote on, you can simply add an entry to the ref table.

Answer 2

Two options for you:

Option One

Wallpaper
- id
- name

Article
- id
- title

Comment
- id
- wallpaper_id <== FK
- article_id   <== FK

Pros: Referential integrity
Cons: You have to modify schema to comment on additional entity types

Option Two

Wallpaper
- id
- name

Article
- id
- title

Comment
- id
- entity_id <== either a wallpaper id or article id
- entity_type <== one of 'wallpaper' or 'article' (use lookup table if you wish)

Pros: You don't have to modify schema to comment on additional entity types
Cons: No referential integrity (although could be enforced via trigger)

Answer 3

I would go for something as simple as adding a field to your Comment table named type, making it a Tinyint. Then when storing a comment, you either make that 0 or 1, choosing which is which type, so whether 0 is wallpaper of article.