这些表如何相关？ [英] How are these tables related?

问题描述

假设我经营一家网上商家，可以透过我的网站订购产品，而且我有一个包含两个表格的资料库：

Say I run an online business where you can order products from my website, and I have a database with two tables:

表 order_number，customer_ID，address

表 customer_ID，first_name，last_name

的客户订单的报告，我将执行LEFT JOIN以连接订单表中的数据，以包括客户的姓名和姓名及其地址和订单号。

To get a full, detailed 'report' of the order, I would perform a LEFT JOIN to concatenate data from the order table to include the customer's first and last names along with their address and order number.

我的问题是，这些表如何相关？他们是吗？实体关系图是什么样子的？

My question is, how are these tables related? Are they at all? What would an entity relationship diagram look like? Separately they don't seem to interact and act more like lookup tables for each other.

推荐答案

订单总是有一个客户，没有？所以它不是一个左边但内部的连接。

An order would always have a customer, no? So it is not a left but inner join.

链接他们的是customer_id。所以你的SQL简单：

What links them is the customer_id. So your SQL is simply:

select o.order_number, o.customer_ID, o.address, 
    c.first_name, c.last_name
from orders o
inner join customer c on o.customer_ID = c.customer_ID;

实体关系：

订购客户
Customer_Id 0 ... N> --- + 1 Customer_Id
... ...

Order Customer Customer_Id 0...N >---+ 1 Customer_Id ... ...

此EF关系来自MS SQL Server的示例数据库Northwind。在样本数据库中，就像你的样例数据库，有客户和订单。客户和订单表通过两个表中的CustomerId字段相关（它是Customers中的主键，Orders表中的外键）。当你将它建模为一个实体关系，而不是你有上面的图。客户实体具有指向特定客户订单的订单导航属性（通过customerId）。订单实体具有指向其客户的导航属性（再次通过CustomerId）。关系是1到0或许多（1 - *），意味着客户可能有0个或更多的订单。

This EF relation is from MS SQL Server's sample database Northwind. In that sample database, just like yours, there are Customers and Orders. Customers and Orders tables are related via the CustomerId fields in both tables (it is the primary key in Customers, and foreign key in Orders table). When you model that as an Entity relation than you have the above diagram. Customer entity have an "Orders" navigation property (via customerId) that points to a particular Customer's Orders. And Order entity have a navigation property that points to its Customer (again via CustomerId). The relation is 1 to 0 or many (1 - *), meaning a Customer might have 0 or more Orders.

从客户端进行加入时，如果您想查看所有客户，无论他们是否有订单，都使用LEFT加入 - 0或更多订单。如果你只想看到那些有订单，那么你使用内联接。

When you do the join from Customer's side, you use a LEFT join "if you want to see all Customers regardless they have Order(s) or not" - 0 or more Order(s). If you want to see only those with Order(s) then you use an inner join.

当您从订单侧执行加入时，订单必须有一个客户，因此它不能是LEFT加入。它是一个INNER加入。

When you do the join from Orders' side, then an Order must have a Customer so it can't be a LEFT join. It is an INNER join.

您可以使用连接的CustomerId字段从双方检查关系。

You can check the relation from both sides using the connecting CustomerId field.

您不会有一个单独的表OrderId，CustomerId，因为它不是多对多关系（它将是纯冗余，并会创建规范化异常）。

You wouldn't have a separate table for "OrderId, CustomerId" as it is not many-to-many relation (it would be pure redundancy and would create normalization anomalies).

希望现在更清楚了。

这篇关于这些表如何相关？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！