Mysql – Contact table for customers and employees

MySQL

I have this relationship that I need to define in my database.

What would be a good way of referring contact details to my employee and to my customer in MYSQL?

I am not sure if the ||——|| is one to one or bidirectional? Which means the Employee can be searched for by contact details?

When I search for ContactDetails, phone number for example to get the Customer or Employee details

Customer-ContactDetails-Employee

Update

Customer would have:
customerId(PK)
maybe other fields too.

Employee would have
employeeId(Pk)
maybe other fields too

ContactDetails would have:
contactDetailsId(PK)
phoneNumber
emailAddress
other fields

I want when I search for the customerId to get the details I have in the Customer table as well as the Contact details. Same for Employee.

How should I model my tables to achieve that?

Best Answer

with this structure you can find every employye that a customer has, only by querying the Customercontacts table, and only if you need more details from the other tables you join them.

edit. with this revision Iadded a date so that you can search for a tine frame where the customer has contact with ione of the empoyees

CREATE TABLE customer (cid BIGINT PRIMARY KEY)

CREATE TABLE customerDeatails (
cdid BIGINT PRIMARY KEY
, cid BIGINT
, Details TEXT
,  FOREIGN KEY (cid) REFERENCES customer(cid) )

CREATE TABLE employee (eid BIGINT PRIMARY KEY)

CREATE TABLE CustomerContactss(
cid BIGINT
, eid BIGINT
,contactdate Datetime
, Details TEXT
, PRIMARY KEY (cid,eid,contactdate)
,  FOREIGN KEY (cid) REFERENCES customer(cid)
,FOREIGN KEY (eid) REFERENCES employee(eid))

db<>fiddle here

Related Question