Example:
4 categories:
- cars
- bikes
- boats
- planes
4类: - 汽车 - 自行车 - 船 - 飞机
and several tables with content
和几个内容表
articles (id, title, body)
photo_galleries (id, photo_filename...)
video_galleres (id, video_title, video_url)
文章(id,title,body)photo_galleries(id,photo_filename ...)video_galleres(id,video_title,video_url)
and now i'm curious how to design database schema to divide content from those tables in to categories...
现在我很好奇如何设计数据库模式以将这些表中的内容划分为类别...
i thought about sth. like this:
我想过......喜欢这个:
categories_content
- category_id
- article_id
- photo_gallery_id
- video_gallery_id
categories_content - category_id - article_id - photo_gallery_id - video_gallery_id
but it seems like a lot waste of space for NULL's... :/
但对于NULL来说,似乎浪费了很多空间......:/
2 个解决方案
#1
2
Another design to consider would be to create separate relationship tables for each content type:
另一个需要考虑的设计是为每种内容类型创建单独的关系表:
article_category (article_id NOT NULL, category_id NOT NULL)
photo_g_category (photo_g_id NOT NULL, category_id NOT NULL)
video_g_category (video_g_id NOT NULL, category_id NOT NULL)
This design eliminates the need to store NULL values, as would be required in your design. All of these columns would be defined as foreign keys to the appropriate tables.
这种设计消除了存储NULL值的需要,这在设计中是必需的。所有这些列都将被定义为适当表的外键。
Wasted space is not really an issue with your design. (In most database engines, no space is used to store a NULL value.)
浪费的空间并不是您设计的真正问题。 (在大多数数据库引擎中,没有空间用于存储NULL值。)
The bigger issue with your design is making sure that at least one of the content FK columns is populated, and allowing the others to be null. Also, your design makes the process of adding and removing relationships more complicated, if you allow more than one content FK column to be populated on a row.
设计中更大的问题是确保至少填充一个内容FK列,并允许其他列为空。此外,如果允许在一行中填充多个内容FK列,则您的设计会使添加和删除关系的过程更加复杂。
How would you plan represent content related to category, e.g. 1 ?
您将如何计划代表与类别相关的内容,例如1?
article: a, b, c photo_g: p, q video_g: v, w, x, y, z
文章:a,b,c photo_g:p,q video_g:v,w,x,y,z
1 a p v
1 b q w
1 c - x
1 - - y
1 - - z
OR
1 a - -
1 b - -
1 c - -
1 - p -
1 - q -
1 - - v
1 - - w
1 - - x
1 - - y
1 - - z
The removal a relationship between category 1 and photo_g p would be different, in one case requiring an update to a row, the other, deleting a row (there's no point in keeping a row where none of the content FK values is populated).
删除类别1和photo_g p之间的关系会有所不同,在一种情况下需要更新一行,另一种情况是删除一行(没有必要保留一行,其中没有填充任何内容FK值)。
I suggest three separate tables to hold these relationships:
我建议使用三个单独的表来保存这些关系:
article_category:
a 1
b 1
c 1
photo_g_category:
p 1
q 1
video_g_category:
v 1
w 1
x 1
y 1
z 1
#2
1
You could have a table for the relationship like:
你可以有一个关系表,如:
category_id, type_of_item, item_id
category_id,type_of_item,item_id
where type_of_item would be article, photo, video etc. and item_id is the id of the item which is in the cateogry.
其中type_of_item将是文章,照片,视频等,而item_id是在cateogry中的项目的id。
#1
2
Another design to consider would be to create separate relationship tables for each content type:
另一个需要考虑的设计是为每种内容类型创建单独的关系表:
article_category (article_id NOT NULL, category_id NOT NULL)
photo_g_category (photo_g_id NOT NULL, category_id NOT NULL)
video_g_category (video_g_id NOT NULL, category_id NOT NULL)
This design eliminates the need to store NULL values, as would be required in your design. All of these columns would be defined as foreign keys to the appropriate tables.
这种设计消除了存储NULL值的需要,这在设计中是必需的。所有这些列都将被定义为适当表的外键。
Wasted space is not really an issue with your design. (In most database engines, no space is used to store a NULL value.)
浪费的空间并不是您设计的真正问题。 (在大多数数据库引擎中,没有空间用于存储NULL值。)
The bigger issue with your design is making sure that at least one of the content FK columns is populated, and allowing the others to be null. Also, your design makes the process of adding and removing relationships more complicated, if you allow more than one content FK column to be populated on a row.
设计中更大的问题是确保至少填充一个内容FK列,并允许其他列为空。此外,如果允许在一行中填充多个内容FK列,则您的设计会使添加和删除关系的过程更加复杂。
How would you plan represent content related to category, e.g. 1 ?
您将如何计划代表与类别相关的内容,例如1?
article: a, b, c photo_g: p, q video_g: v, w, x, y, z
文章:a,b,c photo_g:p,q video_g:v,w,x,y,z
1 a p v
1 b q w
1 c - x
1 - - y
1 - - z
OR
1 a - -
1 b - -
1 c - -
1 - p -
1 - q -
1 - - v
1 - - w
1 - - x
1 - - y
1 - - z
The removal a relationship between category 1 and photo_g p would be different, in one case requiring an update to a row, the other, deleting a row (there's no point in keeping a row where none of the content FK values is populated).
删除类别1和photo_g p之间的关系会有所不同,在一种情况下需要更新一行,另一种情况是删除一行(没有必要保留一行,其中没有填充任何内容FK值)。
I suggest three separate tables to hold these relationships:
我建议使用三个单独的表来保存这些关系:
article_category:
a 1
b 1
c 1
photo_g_category:
p 1
q 1
video_g_category:
v 1
w 1
x 1
y 1
z 1
#2
1
You could have a table for the relationship like:
你可以有一个关系表,如:
category_id, type_of_item, item_id
category_id,type_of_item,item_id
where type_of_item would be article, photo, video etc. and item_id is the id of the item which is in the cateogry.
其中type_of_item将是文章,照片,视频等,而item_id是在cateogry中的项目的id。