数据库模式规范化
以下是 3 个对象 - A、B 和 C 之间的关系。
A:B - 1:M
A:C - 1:M
B:C - M:M，限制是它们必须共享同一个 A 实例。
我当前的架构如下。
a (id, 数据)
b（id，a_id，数据）
c（id，c_id，数据）
b2c(b_id, c_id)
如何设计更好的schema来避免数据不一致？
我知道这篇文章的标题很笼统。如果你们中有人能想到更好的标题，请随意编辑这篇文章。
举个例子，我将开发一个用于生成广告墙的应用程序。广告墙分为许多部分。每个部分都有一个维度（宽度和高度）。有很多广告，每个广告都有一个维度。想象一个广告可以在多个部分展示，并且一个部分可以有多个广告轮播。因此，版块和广告之间的关系是多对多的，但有一个限制，即它们必须具有相同的维度。
Here are the relationships between 3 objects - A, B and C.
A:B - 1:M
A:C - 1:M
B:C - M:M, with the restriction that they must share the same A instance.
My current schema is as follow.
a (id, data)
b (id, a_id, data)
c (id, c_id, data)
b2c (b_id, c_id)
How to design a better schema to avoid data inconsistency?
I know the title of this post is kind of general. If any of you can think of a better title, fell free to edit this post.
As an example, I am going to develop an app for generating an ad wall. An ad wall is divided into many sections. Each section has a dimension (width and height). There are many ads, each of them also has a dimension. Think of an ad can show on multiple sections, and a section can has multiple ads rotating. So the relationship between sections and ads is many-to-many, but with the restriction that they must has the same dimension.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我认为这是应该在代码中强制执行的业务规则。部分：广告为 M:M 已标准化。
但是，我不确定您是否需要
Dimension表（如果您在示例中将其视为“A”表）。I think this is a business rule that should be enforced in code. Section:Ad as M:M is already normalized.
However, I'm not sure you need a
Dimensiontable, if that's what you're thinking as the 'A' table in your example.您已经使用这两个关系定义了 B:C 关系：
A:B - 1:M
A:C - 1:M
我不明白为什么你需要 B:C 关系表。
根据您的示例，我只看到两个表。广告具有宽度和高度，但存在 1:1 关系，因此宽度和高度只是广告表中的字段。截面与宽度和高度也具有 1:1 的关系。只有两张桌子。我也没有看到广告和版块之间存在严格的关系。
You're already defining the B:C relationship with these two relationships:
A:B - 1:M
A:C - 1:M
I don't see why you need a B:C relationship table.
Given your example, I only see two tables. Ads have a width and height, but there's a 1:1 relationship, so width and height are just fields in the ads table. Sections also have a 1:1 relationship to width and height. Only two tables. I don't see a strict relationship between ads and sections, either.