-1

Here I have a EER diagram I am creating in the MySQL editor.

EER Diagram

I want Person to relate to employee. I want some Persons entries to be employees/Customers/etc.

How would I implement this? Would it be a shared key? Join the tables? Related data?

I tried making EmployeeID a foreign key to PersonID. The tables still don't "relate". (There's no way to tell what employee X's name/dob/ect is.)

philipxy
  • 14,867
  • 6
  • 39
  • 83
Nate Diep
  • 13
  • 3
  • EmployeeID remain primary key in Employee table. A foriegn key will be added in employe to link person as Employee. The foriegn key as Person_id will be linked to person table primary key – shariqayaz Dec 07 '22 at 01:48
  • Further, Title could be varchar, Salary should be in Decimal/Float, phone number cant be int, as + sign is defined as country code and int cant be start with zero, if you think that number start from 07xxxxxx, so number should be varchar as well – shariqayaz Dec 07 '22 at 01:51
  • What does "relate to" mean? What is your 1 specific researched non-duplicate question re how/why you are 1st stuck/unsure at what step among which steps following what published presentation of what design method/process given what? PS In the relational model (& ERM) a table represents a relation/relationship/association & that is how we record & query. "Relation" & "relationship" meaning a FK is an ubiquitous abuse of terminology in methods & presentations that misunderstand the relational model. Constraint declarations are for integrity. – philipxy Dec 07 '22 at 02:46
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097)) An ERD is an image of DDL. Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [mre] [ask] [Help] PS Putting words in scare quotes does not clarify the idiosyncratic meaning that you don't make clear by actually saying what you mean. – philipxy Dec 07 '22 at 02:50
  • (Clearly) When this is pinned down & expressed clearly it will be a faq. Moreover the EERM has specific semantics & notation for subtying. PS [How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/3404097) – philipxy Dec 07 '22 at 02:55

1 Answers1

0

I encourage you to try again in the direction of making employeeID refer to personID, as a foreign key. You may not have implemented the whole idea.

The technique has a name: Shared Primary Key. If you search on this phrase, you will find articles describing what you have to do when adding a new employee. Without taking the correct steps when a new employee is added, you break the relationship between the two tables.

There is a tag with this name here in StackOverflow.

What you are basically doing is setting up a situation where employee is a subclass of person. In an object-oriented system, this would be easy. SQL is not object oriented at the base level.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58