关系建模问题
我们有三个实体,分别为 Product
、ProductType
和 ProductCategory
。
假设我们有三种 ProductType
:Book
、Music
和 Video
。
我们为 Book
提供了三种不同的 ProductCategory
:Fiction
、Novel
、Technical
。
音乐
的三种不同ProductCategory
:摇滚
、爵士
、流行
。
我们为 Video
提供了三种不同的 ProductCategory
:小说
、漫画
、戏剧
>。
一个Product
有一个ProductType
并且可以有多个ProductCategory
。但其 ProductCategory
应与其 ProductType
匹配。例如,如果其 ProductType
为 Book
,则只能采用 Fiction
、Novel
和 Technical
作为 ProductCategory
的。
是否可以使用此限制对该架构进行建模(即,Product
的 ProductCategory
应与其 ProductType
匹配),而不使用应用程序代码或触发器等——仅使用表、外键等。
您将如何对此进行建模?
We have three entities called Product
, ProductType
, and ProductCategory
.
Let's pretend we have three kinds of ProductType
: Book
, Music
, and Video
.
We have three different ProductCategory
's for Book
: Fiction
, Novel
, Technical
.
Three different ProductCategory
's for Music
: Rock
, Jazz
, Pop
.
And we have three different ProductCategory
's for Video
: Fiction
, Comic
, Drama
.
A Product
has a ProductType
and can have many ProductCategory
's. But its ProductCategory
's should match its ProductType
. For example, if its ProductType
is Book
, it can only take Fiction
, Novel
, and Technical
as ProductCategory
's.
Is it possible to model this schema with this restriction (i.e. that ProductCategory
's for a Product
should match with its ProductType
) without using application code or triggers, etc -- Just using tables, foreign keys, etc.
How would you model this?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
product_type
product_category
product_type.product_type_id
product_type.product_type_id
) product_category_descriptionproduct
ATEGORY_MAP
PRODUCT.PRODUCT_ID
)PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID
)PRODUCT.PRODUCT_TYPE_ID
<强>和PRODUCT_CATEGORY.PRODUCT_TYPE_ID
)PRODUCT_TYPE
PRODUCT_CATEGORY
PRODUCT_TYPE.PRODUCT_TYPE_ID
)PRODUCT
PRODUCT_TYPE.PRODUCT_TYPE_ID
)PRODUCT_CATEGORY_MAP
PRODUCT.PRODUCT_ID
)PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID
)PRODUCT.PRODUCT_TYPE_ID
andPRODUCT_CATEGORY.PRODUCT_TYPE_ID
)很简单,就是一个简单的两级分类问题。在您的应用中,您需要两个单独下拉菜单,在选择 ProductType 之后填充 ProductCategory。
一项澄清:您的陈述“一个产品有一个 ProductType,并且可以有多个 ProductCategory” 与您的描述相矛盾。一个产品只能有一个产品类别(小说、爵士乐),该类别基于产品类型(书籍、音乐)。
这里不需要代理键(其他建模需求中可能有),它们在这里只是多余的。对于像这样的简单分类,CHAR(1) 或 (2) 更好,用户和开发人员友好(当您扫描输出时,您知道“B”表示“书籍”等),并且比任何数字更快key(当然除了tinyint)。
这里没有“技巧”,它是直接的标准化,它支持您确定的规则。
产品分类链接
我不明白需要“地图”表。
我为 Product 提供了一个代理键,但是当然您需要其他键,以便实现合理的约束。
评论回复
好的,所以您的要求尚不明确,而且现在看来它们正在发生变化。当您在评论中回答我的具体问题时,支持您的要求所需的模型将很容易。为了提供帮助,我发布了两种可能性。当然,它是不完整的,等待您的回答:
链接到两种可能的模型
管理员与用户的控制“紧密度”似乎非常松散。请选择以下选项之一,以便我们可以继续处理并关闭问题:
Product.ProductType 由管理员设置。这允许管理员和用户为 Product.ProductType 选择任何有效的 ProductCategories 以用于他们的任何用途。
对于每个产品,管理员选择 ProductType 和 ProductCategories 子集(从对 Product.ProductType 有效的 ProductCategories 列表中)。用户然后只能使用管理员为产品选择的产品类别,无论他们有什么用途。
请回复,然后我将发布最终版本。
That's easy, it is a simple two-level classifcation issue. in your app, you need two separate drop downs, the ProductCategory is filled after the ProductType has been chosen.
One clarification: Your statement "A Product has a ProductType and can have many ProductCategory's" is contradicted by your description. A Product can have only one Product Category (Fiction, Jazz), which is based on a ProductType(Book, Music).
There is no need for surrogate keys here (there may be in other modelling requirements), they are merely redundant here. For simple classifications like this, CHAR(1) or (2) is much better, user and developer friendly (when you are scanning output, you know "B" means "Book", etc), as well as being faster than any numeric key (except of course tinyint).
There is no "trick" here, it is straight Normalisation, which supports the rules you identified.
Link to Product Classification
I do not understand the need for a "map" table.
I have provided a surrogate key for Product, but of course you need other keys, in order to implement reasonable constraints.
Responses to Comments
Ok, so your requirements are not clear, and it appears they are now changing. When you answer my specific questions in the comments, the model required to support your requirement will be easy. In order to assist, I have published two possibilities. Of course it is incomplete, pending your answers:
Link to Two Possible Models
The "tightness" of your control re administrator vs users seems to be very loose. Please choose one of the following, so that we can progress and close the question:
The Product.ProductType is set by the admin. This allows the admin and users to choose Any of the valid ProductCategories for the Product.ProductType for whatever use they have.
For each Product, the admin chooses the ProductType and a subset of ProductCategories (from the list of ProductCategories that are valid for the Product.ProductType). The users can then use only the ProductCategories chosen by the admin for the Product, for whatever use they have.
Respond please, and then I will publish the final version.