Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2006
    Posts
    20

    enhanced ERD help

    hi could anybody please offer me a little bit of advice for a ERD that i have to produce.
    just a little background. i have to design a ERD for an elephant sanctuary and i am just having trouble getting my head round the relationships between employee, volunteer adn managers with the sanctuary.

    So far i have the following:
    http://img.photobucket.com/albums/v6...afia66/erd.jpg

    i know that volunteer needs to be a subclass of employee because it shares the same attributes as employee as they are given a temporary employee id just wasnt sure whether to do a disjointed sublcass with manager. does this look okay?
    it states that there are pre definied arrival dates and the system needs to record which date the volunteers are arrive and when they leave from the sanctuary. how would i present this? the volunteers also share a bungalow and the system needs to record which room has beeen allocated.
    would i create a weak entity called bungalow?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Volunteers should be stored in the same table as Employee, and simply marked as "Volunteer" or "Unpaid". Ideally, Managers should also be in the Employees table, with a self-referencing foreign from each Employee record to the Employee record of its manager.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2006
    Posts
    20
    hey thanks for the reply. so your saying that volunteer should be an attribute of employee?
    another thing i forgot to mention and show in the diagram is that the volunteer entity has extra attritbutes (email, passport number and insurance details) plus it shares all the attributes of the employee entity. thats why i decided to make it a subclass of employee. well it seemed to make sense?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    What, employees can't have e-mails, passports, or insurance details?

    Yes, "Volunteer" is an attribute of employee. Go ahead and include the volunteer-specific attributes in the Employees table, even if they are not used for all records. In database design, we try to avoid splitting out data into separate tables. You can think of database schemas being partially object oriented (especially 3rd normal form designs), but they do not have inheritance between tables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2006
    Posts
    20
    yeh only volunteers have passport number, insurance details (composite). they also have a start and leave date. so Persumambly i create a separate relation to the company from the actual employees?
    volunteers are also given a temporary employee id. would it be sensible to create another attribute called Volunteer id?
    thanks again

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    No, you should do exactly what I keep telling you to do.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by blindman
    No, you should do exactly what I keep telling you to do.
    Even though I agree wholeheartedly in this case, the mere thought of telling someone that they should blindly do what a blindman says just frightens me... Particularly a blindman that may be a crustier curmudgeon than I am!

    -PatP

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    As long as Rudy is on the forum I am at most Crusty Curmudgeon #2.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    but honestly, i am trying to be more friendly lately...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Yay! I'm #1! I'm #1!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •