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
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
db<>fiddle here