找到你要的答案

Q:detail in detail sql

Q:详细介绍了SQL

I have something to ask, hope you guys can give me some advices.

I have many to many relationship between [ORDER] table and [PRODUCT] table.

Now the condition is I need one table again called [PACKAGE] table that have many to many relationship too with [PRODUCT] table.

In this case I want to link the [PACKAGE] table to [ORDER] table. Because The condition is people can order item or order package in the same order form.

Is it the true way if i just link the [PACKAGE] table to [ORDER] table ?

我有些事情要问,希望你们能给我一些建议。

“订单”表与[产品]表之间有多对多关系。

现在的条件是,我需要一个表再次被称为[包]表有许多关系太多[产品]表。

在这种情况下,我想将[包]表链接到[订单]表。因为条件是人们可以按相同的顺序订购项目或订单包。

如果我把[包]表与[订购]表联系起来,这是真的吗?

answer1: 回答1:

Here are two options.

The first is to introduce a new table called OrderPackages which provides the link to the packages. However, I don't like this approach, because you cannot easily query for all the items in an order. The items in the order could be in both OrderItems and OrderPackages.

SQL is pretty bad at "one-of" types of relationships. This is what you really want -- an order linked either to an item or to a package, but not both.

My recommendation is to wrap all the items in their own package, and then have an OrderPackages table and a PackageItems table. This will make all the querying consistent, and should be pretty easy to implement in any database.

You can automatically create a new package for a new item by using a trigger or wrapping the insert code in a stored procedure that does the insert in both tables.

这里有两个选项。

首先是引入了一个新的表称为orderpackages为包提供的链接。但是,我不喜欢这种方法,因为你不能轻易地查询订单中的所有项目。在订单的项目可以在orderitems和orderpackages。

SQL是很不擅长“之一”类型的关系。这是你真正想要的-一个订单链接到一个项目或一个包,但不是两者。

我的建议是把自己包的所有项目,然后有一个orderpackages表和packageitems表。这将使所有的查询一致,并且应该很容易在任何数据库中实现。

您可以通过使用触发器或在两个表中插入的存储过程包插入代码来自动创建新项目的新包。

sql  many-to-many  relationship