E-R Model

Posted on 2009-02-08
Last Modified: 2012-05-07
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.

Question by:duta
    LVL 5

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

    Author Comment

    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!
    LVL 44

    Expert Comment

    Why not put the Employees in the Customers table?

    Author Comment

    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?

    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    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.


    Author Comment

    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.

    Author Comment

    On the diagram, I drew it, using MS Visio 2007.

    Thanks !
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    I suggest you test GRayL's post: http:#a23587153

    Author Comment

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


    Author Comment

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now