0

I have this image table,

image_id
image_title
image_description
image_source

and I want this image table to associate with page table and sometimes with comment table

page table,

page_id
...
...

comment table,

comment_id
...
...

Should I put the foreign key of page_id and comment_id in image table?

image_id
image_title
image_description
image_source
page_id
comment_id

Or should I create a map table for each purpose?

a map table for image and page

image_id
page_id

and another map table for image and comment

image_id
comment_id

What is the best practice for this kind of scenario?

Will the image table get slow if I use map idea when the image table row increase (this is another main concern)?

Currently I do this,

image page table,

image_id
image_title
image_description
image_source
page_id

image comment table,

image_id
image_title
image_description
image_source
comment_id

but this doesn't look good as I am repeating the columns and it does not appear to be dynamic...

Any thoughts?

EDIT:

Sometimes the image is associated with the page and sometimes with comment. The upload image sometimes is for the article itself and sometimes is for the comment under the article.

image_id  image_title  page_id  comment_id
1         a            1        NULL
2         b            NULL     1
3         c            1        NULL
4         d            1        NULL
Run
  • 54,938
  • 169
  • 450
  • 748
  • What do you mean by `sometimes`? It's not very clear what the model *or* the problem is. – Matt Fenwick Feb 21 '12 at 16:37
  • Hi Matt, please see my edit above. Hope it makes sense. thanks. – Run Feb 21 '12 at 16:45
  • "should I create a map table for each purpose?" That depends on whether a page/comment can have multiple images in it; if it's so that would make sense. I've done that for contact-data once, which could get assigned to a user or a company. – feeela Feb 21 '12 at 16:57
  • Yes a page/comment should be allowed to have multiple images in it... – Run Feb 21 '12 at 17:06

2 Answers2

1

Your Image table is fine. You should create a Comment table and a Page table and store the ImageId in those tables.

EDIT: (Didn't read all the comments)

Based on the requirement that Pages and Comments can have multiple images then the only approach is to creat two linking table one that links the Comments to Images and one that links the Pages to Images.

Michael Riley - AKA Gunny
  • 5,074
  • 4
  • 42
  • 89
1

There are limitations with the design you have now. For example, an image cannot be related to both a page and a comment. Unless its data is duplicated in the two Image tables. Second and more important, (and as you say, it doesn't look good), you have two tables with almost identical columns and functionality.

So, having one Image table and two mapping tables looks much better design. If you later want to store more (and different) information regarding these mappings - for example, you may want to add a last_changed timestamp and a size limitation for PageImage (max 100KB per image) and a number limitation for CommentImage (max 3 per comment) - you don't have to change the overall design, only these 2 specific mapping tables.

You may, however, find yourself creating a bunch of mapping tables, say one more for UserImage, another for MessageImage and yet another one for EmailImage, all having identical (or almost identical) structure - and this again leads to duplication of code.

To eliminate such duplication, you can use the supertype/subtype pattern. Create a supertype Entity, with Page, Comment, User, Message, Email as subtypes (every one of those child entities will have a Primary Key that is also a Foreign Key to the supertype Entity).

Then, your mappings can easily be combined in one EntityImage table (with FKs to Image and Entity).

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • Thanks so much for the answer, ypercube. Can I ask - what is `supertype/subtype pattern`? What difference of it from the `mapping` tables in my OP? – Run Feb 22 '12 at 12:47
  • From another question: [Same data from different entities in Database - Best Practice - Phone numbers example](http://stackoverflow.com/questions/5466163/same-data-from-different-entities-in-database-best-practice-phone-numbers-ex/5471265#5471265) – ypercubeᵀᴹ Feb 22 '12 at 12:52
  • The `parties` there is your `Entity`. The `person`, `organization` tables there are your `Page` and `Comment`. After you create your supertype, you can make relationships from other tables to it (1-to-many, 1-to-1, many-to-many, whatever). And thus you are making a relationship to the union of all the subtype entities. – ypercubeᵀᴹ Feb 22 '12 at 12:54
  • Thanks for the replies. This is new to me. Going to spend more time digesting it. But thanks! – Run Feb 22 '12 at 13:48