问题描述
我有这个图像表
,
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
/ pre>
page_id
另一个
地图
表格的图像和评论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 withpage table
and sometimes withcomment table
page table,
page_id ... ...
comment table,
comment_id ... ...
Should I put the foreign key of
page_id
andcomment_id
inimage
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 pageimage_id page_id
and another
map
table for image and commentimage_id comment_id
What is the best practice for this kind of scenario?
Will the
image
table get slow if I usemap
idea when theimage
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 forPageImage
(max 100KB per image) and a number limitation forCommentImage
(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 forMessageImage
and yet another one forEmailImage
, 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
, withPage
,Comment
,User
,Message
,Entity
).Then, your mappings can easily be combined in one
EntityImage
table (with FKs toImage
andEntity
).这篇关于一张图表可以服务两张外表吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!