一张图表可以服务两张外表吗？ [英] An image table to serve two foreign tables?

问题描述

我有这个图像表，

  image_id 
 image_title 
 image_description 
 image_source 
  

我想要这个 image table 与页面表和有时与 注释表

页表，

  page_id 
 ... 
 ... 
  

评论表，

  comment_id 
 ... 
 ... 
  

我应该将外键 page_id 和 comment_id
$
image_description
image_source
page_id
comment_id

或我应该为每个目的创建一个地图表

a map 表格图像和页面

  image_id 
 page_id 
  / pre> 
 
 
另一个地图表格的图像和评论
  image_id 
 comment_id 
  
   
      当图像表格行增加时，如果我使用 map  （这是另一个主要问题）？
 
 
 
目前我这样做
 
 
 
  image页面表，
  image_id 
 image_title 
 image_description 
 image_source 
 page_id 
  
  image comment  table ，
  image_id 
 image_title 
 image_description 
 image_source 
 comment_id 
  
但这并不好看，因为我重复列，它似乎不是动态的... 
 
 
 
任何想法？
 
 
 
 编辑： 
 
 
 
有时，图像与页面相关联，有时与评论。上传图片有时是文章本身，有时是针对文章下的评论。
  image_id image_title page_id comment_id 
 1 a 1 NULL 
 2 b NULL 1 
 3 c 1 NULL 
 4 d 1 NULL 
  
 
 
解决方案
现在有了设计的局限性。例如，图像不能与页面和注释都相关。除非它的数据在两个Image表中重复。第二个更重要的是（和你说的那样看起来不太好），你有两个表和几乎相同的列和功能的表。 
 
 
 
所以，有一个 Image 表和两个映射表看起来更好的设计。如果您以后想要存储关于这些映射的更多（和不同的）信息 - 例如，您可能需要为 PageImage 添加last_changed时间戳和大小限制（最多100KB每个图像）和 CommentImage （每个注释最多3个）的数量限制 - 您不必更改总体设计，只有这两个特定的映射表。
 
 
 
然而，您可能会发现自己创建了一堆映射表，另外针对 UserImage 另外一个映射表，另一个用于 MessageImage ，另一个 EmailImage ，都具有相同（或几乎相同）的结构，这又会导致代码重复。 
 
 
 
为了消除这种重复，可以使用超类型/子类型模式。创建一个超类型实体，其中页面，注释，用户，消息，电子邮件作为子类型（每一个子实体将有一个主键，也是超类型 Entity 的外键）。
 
 
 
然后，您的映射可以轻松地组合在一个 EntityImage 表（FK到 Image 和 Entity ）。
 
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

解决方案
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).

                        
这篇关于一张图表可以服务两张外表吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！