哪种表架构更好?

发布于 2024-12-14 13:18:39 字数 501 浏览 2 评论 0原文

哪种结构更好?

table1

postid category1 category2 category3
2            a         b        d

3            a         c       null

post表

postid
2
3

category_option表

category     option

category1      a
category2      b
category3      c
category4      d

option_post表

post option
2      a
2      b
2      d 
3      a
3      c

似乎为第一个结构构建查询比第二个结构更容易。

which structure is better?

table1

postid category1 category2 category3
2            a         b        d

3            a         c       null

or

post table

postid
2
3

category_option table

category     option

category1      a
category2      b
category3      c
category4      d

option_post table

post option
2      a
2      b
2      d 
3      a
3      c

it seems buiding query for first structure is easier than second structure.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(6

滴情不沾 2024-12-21 13:18:39

这两种结构模拟了不同的事物。第一个严格地只允许(最多)3 个类别(并按位置区分类别),而第二个可以建模任意数量的类别(不按位置区分)。哪个更好实际上取决于您想要完成的任务...

在纯粹的技术层面上,第二个可能需要对某些查询进行 JOIN,其中第一个可以满足来自单个(且唯一)表的查询。这是否是一个问题,再次取决于具体情况......

These two structures model different things. The first one rigidly allows only (up to) 3 categories (and differentiates between categories by position), while the second one can model any number of categories (which are not distinguished by position). Which one is better really depends on what you are trying to accomplish...

On purely technical level, the second one might require a JOIN for some queries where the first one could satisfy the query from the single (and only) table. Whether this is a problem or not, again, depends on circumstances...

双马尾 2024-12-21 13:18:39

取决于要求...

您预计随着时间的推移会增加选项的数量吗?

您的第一个选项到目前为止更容易编码,第二个选项是更加模块化的设计和可扩展性。

Depends on requirements...

Do you anticipate increasing the number of options over time?

your first option is by far easier to code, the second option is much more modular design and scalable.

习惯成性 2024-12-21 13:18:39

这在很大程度上取决于类别的性质。如果列表是固定的并且不太可能增长,那么第一个结构就可以正常工作并且更容易使用。如果类别列表可能会增长,那么第二个选项会增长得更好。

如果类别值稀疏也很重要。如果大多数类别没有值,那么第二种方法将占用更少的空间。如果每个项目在每个类别中都有值,那么这不是问题。

在这种情况下,了解“可能”的含义很重要。这并不意味着你作为设计师认为它不会增长。这意味着类别列表已被充分理解且成熟,因此不太可能增长。我一直在寻找例子,但没有想到。

选择第一个选项有充分的理由,但要小心——在生产系统中切换到第二个选项将是一场噩梦。

It depends greatly on the nature of the categories. If the list is fixed and unlikely to grow, then the first structure works just fine and can be easier to work with. If the list of categories is likely to grow, then the second option will grow better.

It also matters if the category values are sparse. If most of the categories will not have values, then the second approach will take up much less space. If every item will have values in every category, this is not an issue.

It is important in this case to understand what "likely" means. It doesn't mean that you the designer don't think it will grow. It means that the list of categories is well-understood and mature, and so unlikely to grow. I kept looking for examples, but none come to mind.

There are good reasons to select the first, but do so with care - switching to the second option in a production system will be a nightmare.

羁绊已千年 2024-12-21 13:18:39

The second one is better. The first is a violation of First Normal Form:

http://en.wikipedia.org/wiki/First_normal_form#Repeating_groups_across_columns

少女净妖师 2024-12-21 13:18:39

第2个更好一点这是典型的带有连接表的many2many 情况。

如果你用第一种方式来做,如果有新的类别4、5、6、7、8...来了,你会怎么做?添加新列到表中?

而且,我不知道您是否有这样的要求,“有多少帖子带有类别选项‘c’”?
第二个很容易统计,但是第一个......

2nd one better. it's typical many2many with join table case.

if you do it in 1st way, what are you gonna do if there are new category category 4,5,6,7,8... come? add new columns to the table?

And, I don't know if you have a requirement like, "how many posts with category option 'c'"?
2nd one is easy to do the statistic, but the 1st one...

余罪 2024-12-21 13:18:39

category_option 必须具有类别 id 才能与 opton_post 建立联合表,否则无法创建第二个表结构。

通过该结构,您可以在这里实现两件事。

1)与帖子和类别建立一对一的关系。 (这意味着您可以在将来添加更多类别(如果需要)

2)在此结构中自动避免空值。这意味着不再处理表或 SQL 查询中的空值。

希望这有帮助。

category_option must have category id to make a conjunction table with opton_post otherwise there is no mean to create that 2nd table structure.

two thing you can achieve here with that structure.

1) make a one to one relationship with post to category. (this mean you can add more categories in future if its needed)

2) in this structure null values automatically avoided. this mean no more handling null values in table or in sql queries.

hope this helps.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文