连接客户表和员工表的外键？

Question

我有两个表：

Employee ，其中包含以下列：

EmployeeID	FirstName	LastName	Address	Salary	Department	Position

Customer ，其中包含以下列：

CustomerID	FirstName	LastName	Address	Company

我还有另外两个表：

>产品：

产品ID	产品	价格

购买：

产品ID	客户ID	购买日期	库存

这是我需要创建的查询：

• 员工是客户报告 - 员工姓名、购买的产品、 产品成本，每位员工购买所有产品的总成本

通过查看数据，我已经知道所有客户也是员工。我需要在客户表或员工表中创建外键才能编写此查询。这就是我被困住的地方。

Answer 1

你有几种可能性。最简单的方法是对两者使用相同的号码，在这种情况下，您需要一种机制来阻止员工号码被客户使用。保留数字范围是一种简单的方法。
如果没有，您可以向员工添加一个 customer_number 列，这将是一个外键。
对于前三个表（Employee、Customer 和 Product），主键是 ID 列。对于购买，您需要创建一个列，因为同一名员工可以多次购买相同的产品。自动增量列作为主键会更简单。 2 个 ID 列是外键。

Answer 2

来自评论：

<块引用>

当员工是客户时，EmployeeID 和 CustomerID 匹配吗？如果是这样，那已经是您的外键了。如果不是，您如何确定特定员工何时是特定客户？

不幸的是，它们不匹配。根据名字、姓氏和地址，客户是员工

您不希望存在外键关系；相反，当您显示数据时，您需要连接 firstname、lastname 和 address 上的表。

SELECT c.firstname || ' ' || c.lastname AS name
       -- other columns you want to display
FROM   customers c
       INNER JOIN employees e
       ON (   c.firstname = e.firstname
          AND c.lastname  = e.lastname
          AND c.address   = e.address)
       -- and then join the product and purchase tables to get the other data

但是，当您的 John Smith 和 Joanne Smith 居住在同一地址并且他们都以 J Smith 的身份存储在您的数据库中时，他们将即使他们不是同一个人，也能匹配。或者，您在客户表中有一个名为 Joanne Smith 的人，在员工表中名为 Jo Smith，那么他们将不匹配，即使他们是同一个人。等等。

您想要找到比匹配姓名和地址更好的解决方案。

我需要在客户表或员工表中创建外键才能编写此查询。

不，您真正想做的是将表重构为第三范式，这样就不会重复日期并确保 id 列在 customers 和 之间匹配员工；您可以通过包含 people 表来做到这一点：

CREATE TABLE people (
  id        NUMBER(10,0)
            GENERATED ALWAYS AS IDENTITY
            CONSTRAINT people__id__pk PRIMARY KEY,
  firstname VARCHAR2(100)
            NOT NULL,
  lastname  VARCHAR2(100)
            NOT NULL,
  address   VARCHAR2(500)
            NOT NULL
);

CREATE TABLE employees (
  id         CONSTRAINT employees__id__pk PRIMARY KEY
             CONSTRAINT employees__id__fk REFERENCES people,
  salary     NUMBER(10,2),
  department NUMBER(5,0),
  position   VARCHAR2(100)
);

CREATE TABLE customers (
  id         CONSTRAINT customers__id__pk PRIMARY KEY
             CONSTRAINT customers__id__fk REFERENCES people,
  company    VARCHAR2(100)
);

然后您就知道客户和员工是否具有相同的 id 那么他们是同一个人。