Foreign key to connect customer and employee table?-CodePudding

I have two tables:

Employee with the columns:

EmployeeID	FirstName	LastName	Address	Salary	Department	Position

Customer with the columns:

CustomerID	FirstName	LastName	Address	Company

I also have two other tables:

Product:

ProductID	Product	Price

Purchase:

ProductID	CustomerID	PurchaseDate	Inventory

This is the query I need to create:

Employee is Customer Report - Employee Name, Product Purchased, Product Cost, Total Purchased For All Products Per Employee

By looking at the data, I already know that all of the customers are also employees. I need to create a foreign key in either the customer table or the employee table in order to write this query. This is where I am stuck.

CodePudding user response：

You have several possibilities. The simplest would be to use the same number for both, in which case you need a mechanism to stop an employees number being used for a customer. A reserved range of numbers would be a simple way.
If not you could add a customer_number column to employees, which would be a foreign key.
For the first three tables, Employee, Customer and Product, the primary key is the ID column. For purchases you need to invent a column as the same employee could buy the same product more than once. An auto increment column as primary key would be simpler. The 2 ID columns are foreign keys.

CodePudding user response：

From comments:

When an Employee is a Customer, do the EmployeeID and CustomerID match? If so, that's already your foreign key. If they don't, how do you determine when a specific Employee is a specific Customer?

They don't match unfortunately. Customers are employees based on the firstname, lastname and address

You do not want a foreign key relationship; instead when you display the data you would need to join the tables on the firstname, lastname and 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

However, when you have John Smith and Joanne Smith living at the same address and they are both stored in your database as J Smith then they will match even though they are not the same person. Or you have one person in the customer table under the name Joanne Smith and in the employee table called Jo Smith then they will not match, even though they are the same person. Etc.

You want to find a better solution than matching on names and addresses.

I need to create a foreign key in either the customer table or the employee table in order to write this query.

No, what you really want to do is refactor your tables into third-normal form so that you do not duplicate date and ensure the id columns match between customers and employees; you can do that by including a people table:

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)
);

Then you know if a customer and an employee has the same id then they are the same person.