Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • Last Modified:

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.

1 Solution

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>
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?

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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.


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now