I have 3 types of content: blogs, press releases, and reminders. All of them have a body and entered by fields. The blogs and press releases have a title field, which the reminder lacks, and the reminders has an hour field, which blogs and press releases lack. This is what it looks like in tabular format so it's easy for you to see...
我有3种类型的内容:博客,新闻稿和提醒。他们都有一个身体,并按字段进入。博客和新闻稿都有一个标题字段,提醒者缺少标题字段,而提醒文件有一个小时字段,博客和新闻稿缺乏。这是表格格式的样子,所以你很容易看到......
blog press release reminder
---------------------------------------------------
entered by field yes yes yes
body field yes yes yes
title field yes yes --
time field -- -- yes
I'm creating a main table called content that links to the specialized tables blogs press releases reminders. I thought of 2 structures
我正在创建一个名为内容的主表,链接到专门的表博客新闻稿提醒。我想到了2个结构
First structure... This is how the content management system I use does it, but I don't want to follow in their steps blindly because my needs are not the same. Put ALL shared fields in the main content table. So the content table will not only have type and type id to link to the specialized tables, the content table will also have the common fields like body and entered by. The other 3 tables only have their unique fields.
第一种结构......这就是我使用的内容管理系统的功能,但我不想盲目地遵循他们的步骤,因为我的需求并不相同。将所有共享字段放在主内容表中。因此内容表不仅具有链接到专用表的类型和类型id,内容表还将具有诸如body和输入的公共字段。其他3个表只有它们唯一的字段。
content table B=blogs table PR=press releases table R=reminders table
------------------------------------------------------------------------------
id id id id
type=B/PR/R title title hour
type id
body
entered by
Second structure. content table only has the type and type id necessary to link to the other 3 tables, This means that the common fields get repeated in the 3 tables.
第二种结构。内容表只有链接到其他3个表所需的类型和类型ID,这意味着公共字段在3个表中重复。
content table B=blogs table PR=press releases table R=reminders table
------------------------------------------------------------------------------
id id id id
type=B/PR/R entered by entered by entered by
type id body body body
title title hour
Which should I go with? I thought the first structure is better because I can search all content whether it's a blog or press release or reminder for a specific word. I still have to look in the other tables if I want to search the title which is available only to blogs and press releases, but...
我应该选哪个?我认为第一个结构更好,因为我可以搜索所有内容,无论是博客,新闻稿还是特定单词的提醒。如果我想搜索仅适用于博客和新闻稿的标题,我仍然需要查看其他表格,但是......
So which structure is better, and why you think so? I'm also open to other ideas or improvements that are different from these 2.
那么哪种结构更好,为什么你这么想?我也愿意接受与这些不同的其他想法或改进2。
4 个解决方案
#1
2
The first one is the better construct, it allows for a content to have a specific set of required or common data in the content table and then specialized data in the child tables. This also allows you to add more types in the future with other requirements that still reuse the common elements in content but retain any unique data.
第一个是更好的构造,它允许内容在内容表中具有一组特定的必需或公共数据,然后在子表中具有专用数据。这也允许您在将来添加更多类型,其他需求仍然可以重用内容中的公共元素,但保留任何唯一数据。
One other key question is if that data is required, for example do all reminders require an hour and do all blogs/press release require a title. If they are required then you ensure that those child tables will always be populated. If they are not then perhaps you should look at flattening the structure (yes Virginia you should sometimes denormalize).
另一个关键问题是,如果需要这些数据,例如,所有提醒都需要一个小时,并且所有博客/新闻稿都需要标题。如果需要它们,则确保始终填充这些子表。如果他们不是那么也许你应该看看扁平化结构(是弗吉尼亚,你有时应该非规范化)。
So instead your content table simply becomes (nn = not null, n = nullable) id (nn) ,type id (nn), type (nn), body (nn), entered by (nn), title (n), hour (n). The main reason I usually find for doing this is that if the different data entities you are creating are so similar that over time it is possible they will merge. For example reminders at this time do not require a title, but in the future the might.
所以你的内容表只是变成(nn = not null,n = nullable)id(nn),type id(nn),type(nn),body(nn),由(nn)输入,title(n),小时(N)。我通常发现这样做的主要原因是,如果您创建的不同数据实体非常相似,那么随着时间的推移,它们可能会合并。例如,此时的提醒不需要标题,但将来可能需要。
#2
3
The first structure is a classic super type-subtype approach, and recommended. I would just suggest naming primary keys with full table-name-id like ContentID to avoid possible confusion.
第一种结构是经典的超类型 - 子类型方法,并推荐使用。我建议使用完整的table-name-id命名主键,如ContentID,以避免可能的混淆。
#3
1
I would sooner go without any sort of "type" field, instead making four tables: content, blogs, pressreleases and reminders. Content has the common fields enteredby, body, and title. For each of blogs, pressreleases and reminders, they have an id that is a primary key and also a foreign key to a content id. This makes a 1:1 "is-a" relationship. reminder can have the additional time field. To determine what type of entry a content row is, do a join select.
我很快就会没有任何类型的“类型”字段,而是制作四个表:内容,博客,新闻发布和提醒。内容具有输入,正文和标题的公共字段。对于每个博客,新闻发布和提醒,他们都有一个id是主键,也是内容id的外键。这使得1:1“is-a”关系。提醒可以有额外的时间字段。要确定内容行的条目类型,请执行连接选择。
This may not be the best in terms of performance but it's better normalized.
这在性能方面可能不是最好的,但它更好地规范化。
#4
0
I think you should think about the common fields. Do they really need to match?
我认为你应该考虑共同领域。他们真的需要匹配吗?
If they need to match, it's easier to just put it in a single table.
如果他们需要匹配,将它放在一个表中就更容易了。
#1
2
The first one is the better construct, it allows for a content to have a specific set of required or common data in the content table and then specialized data in the child tables. This also allows you to add more types in the future with other requirements that still reuse the common elements in content but retain any unique data.
第一个是更好的构造,它允许内容在内容表中具有一组特定的必需或公共数据,然后在子表中具有专用数据。这也允许您在将来添加更多类型,其他需求仍然可以重用内容中的公共元素,但保留任何唯一数据。
One other key question is if that data is required, for example do all reminders require an hour and do all blogs/press release require a title. If they are required then you ensure that those child tables will always be populated. If they are not then perhaps you should look at flattening the structure (yes Virginia you should sometimes denormalize).
另一个关键问题是,如果需要这些数据,例如,所有提醒都需要一个小时,并且所有博客/新闻稿都需要标题。如果需要它们,则确保始终填充这些子表。如果他们不是那么也许你应该看看扁平化结构(是弗吉尼亚,你有时应该非规范化)。
So instead your content table simply becomes (nn = not null, n = nullable) id (nn) ,type id (nn), type (nn), body (nn), entered by (nn), title (n), hour (n). The main reason I usually find for doing this is that if the different data entities you are creating are so similar that over time it is possible they will merge. For example reminders at this time do not require a title, but in the future the might.
所以你的内容表只是变成(nn = not null,n = nullable)id(nn),type id(nn),type(nn),body(nn),由(nn)输入,title(n),小时(N)。我通常发现这样做的主要原因是,如果您创建的不同数据实体非常相似,那么随着时间的推移,它们可能会合并。例如,此时的提醒不需要标题,但将来可能需要。
#2
3
The first structure is a classic super type-subtype approach, and recommended. I would just suggest naming primary keys with full table-name-id like ContentID to avoid possible confusion.
第一种结构是经典的超类型 - 子类型方法,并推荐使用。我建议使用完整的table-name-id命名主键,如ContentID,以避免可能的混淆。
#3
1
I would sooner go without any sort of "type" field, instead making four tables: content, blogs, pressreleases and reminders. Content has the common fields enteredby, body, and title. For each of blogs, pressreleases and reminders, they have an id that is a primary key and also a foreign key to a content id. This makes a 1:1 "is-a" relationship. reminder can have the additional time field. To determine what type of entry a content row is, do a join select.
我很快就会没有任何类型的“类型”字段,而是制作四个表:内容,博客,新闻发布和提醒。内容具有输入,正文和标题的公共字段。对于每个博客,新闻发布和提醒,他们都有一个id是主键,也是内容id的外键。这使得1:1“is-a”关系。提醒可以有额外的时间字段。要确定内容行的条目类型,请执行连接选择。
This may not be the best in terms of performance but it's better normalized.
这在性能方面可能不是最好的,但它更好地规范化。
#4
0
I think you should think about the common fields. Do they really need to match?
我认为你应该考虑共同领域。他们真的需要匹配吗?
If they need to match, it's easier to just put it in a single table.
如果他们需要匹配,将它放在一个表中就更容易了。