E-R Model

Dear Experts:

I am creating a very simple E-R model of  retailing business.

Here is the scanario:

A customer places an order, which is recorded into Sale table.  The Sale table should record of the customer and the employee who helped the sale.
The problem with which I need your help is:

The company's employees can also become clients.
How to display this recursive (or sub type-super type relations?) in an E-R diagram?

I spent quite much time trying to figure out.
If you may kindly show me a diagram, it will be tremendously appreciated.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


a) create a single entity 'people' which represents both clients and employees; or

b) create a single abstracted entity 'people', then two entities related to that... 'clients' and 'employees', then a relationship from each 'clients' and 'employees'  to 'sales'

In practise, a FK on the 'clients' table, and a FK on the 'employees' table will link each record to the 'people' table, so a person can be an employee, a client, both, or neither.

The 'people' table would include standard information (such as name, address), and the 'employee'/'client' tables would have information such as employee ID.

              <can be> -- 1 [Employee] 1 -------<assists>
             /                                                                 \
         1 /                                                                    \ n
[People]                                                                 [Sales]
         1 \                                                                 / n
               <can be> -- 1 [Client] 1 -- <purchases>

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dutaAuthor Commented:
Thank you so much for your kind, prompt.

I am sorry for not responding sooner.

I am writing upon reading your kind tip.

Can you kindly take a look at the screen print below and to let me know whether
I am making a right approach?

I would like to learn the best possible E-R model from the given scenario.

Thanks again!
Why not put the Employees in the Customers table?
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

dutaAuthor Commented:
Genius said: Why not put the Employees in the Customers table?

Duta:  Can you kindly elaborate on that?

How can I put employees in Customer table?

How can I distinguish employee from customers?

Can you kindly give me even a sketch of your great idea?

Jeffrey CoachmanMIS LiasonCommented:

Is this a project for school?

The ER diagrams you are posting are not the same ones that Access can generate.

And most of the time "beginners" don't throw around terms like "E-R" and "Recursive", except in an academic setting.

dutaAuthor Commented:
Genius,  yes, I am finishing my assignment for a class.  I am done except the recursive thing.

If my question violates the site policy, please feel free to delete my question.

I  consulted with several  books and spent many hours in online research before posting the question.

The recursive things are quite confusing.  So far no one, including the authors of the books and the instructors, has explained it clearly.

Hope I clarified adequately.
dutaAuthor Commented:
On the diagram, I drew it, using MS Visio 2007.

Thanks !
Jeffrey CoachmanMIS LiasonCommented:

I suggest you test GRayL's post: http:#a23587153
dutaAuthor Commented:
TO: bitter chicken:

YOU SAID: n practise, a FK on the 'clients' table, and a FK on the 'employees' table will link each record to the 'people' table, so a person can be an employee, a client, both, or neither.

MY QUESTION:  May I understand that I need to create three entities: (1) super type People table and (2) two sub type tables: Customer and Employee.   Should they share the same PK (e.g., PeopleID)?

dutaAuthor Commented:
To Moderator:

If this question does not comply with the site policy or rule, please feel free to delete it.

I am a person who strives to strictly and fully comply with all laws and regulations.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.