locked
Simple phone book design question RRS feed

  • Question

  • Hi,

    I have to develop a small tool for the local goverment office where I work.

    One part of this tool is the phone book.

    I have employees that may work in different office (room), so they could have one or more phone number (one for each room they move). In an office (room) there could be a telephone not assigned to an employee (so it is the office phone number).

    Initially I've designed the database in this way :

    but now I'm thinking to change it in this way:

    In this way I could also insert external contacts (other government offices and so on).

    The PhoneBookContact has simply a Name field (not shown) that could be imported from Employee or Office or manually inserted for external contacts.

    What do you think about it?

    Thursday, December 13, 2018 3:02 PM

Answers

  • Hi Visakh16,

    do you mean to transform the E_O (EmployeeRoom) relationship in a ternary relationship removing the PhoneBookContact table ?

    If so, I could only have internal (within my local office) phone numbers.

    Nope

    A table which will have phonenumber,type,EMployeeOfficeID,OfficeID

    EMployeeOfficeID will have relationship key for EMployee -> Office
    OfficeID for external offices

    Have a constraint to ensure either one of them has value always (CHECK constraint)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, December 13, 2018 3:41 PM
  • Hi Emily,

    well, not exactly.

    Only now I'm starting to realize that I need to keep track of what telephone number is assigned to en employee depending on the office (room) where he works.

    So if Mr. Brown works both in "Office A" and "Office B", he will have two telephone number, but they must also be related to each office.

    So, probably the entry in the PhoneBookContact, in these cases, should have both foreign key (Employee and Office).

    When an employee works only in one office , the office foreign key should be null.

    If the telephone number is assigned to an office only, the employee foreign key must be null.

    Friday, December 14, 2018 9:02 AM

All replies

  • I would add table for Phone numbers with EmployeeRoomId(id of the table containing employee room relationship) as a foreignkey in it. You can also add a master table to indicate type of phone (office,home,cell etc) and refer its id in your table.

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, December 13, 2018 3:25 PM
  • Hi Visakh16,

    do you mean to transform the E_O (EmployeeRoom) relationship in a ternary relationship removing the PhoneBookContact table ?

    If so, I could only have internal (within my local office) phone numbers.

    Thursday, December 13, 2018 3:37 PM
  • Hi Visakh16,

    do you mean to transform the E_O (EmployeeRoom) relationship in a ternary relationship removing the PhoneBookContact table ?

    If so, I could only have internal (within my local office) phone numbers.

    Nope

    A table which will have phonenumber,type,EMployeeOfficeID,OfficeID

    EMployeeOfficeID will have relationship key for EMployee -> Office
    OfficeID for external offices

    Have a constraint to ensure either one of them has value always (CHECK constraint)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, December 13, 2018 3:41 PM
  • Hi liuc,

     

    Your change will meet your demands. I agree with it.

     

    By the way, my understanding is that the telephone has been assigned to an employee will not be recorded again as the office phone number in the PhoneBookContact table. It is just be recorded as the employee phone number. Right?

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 14, 2018 8:14 AM
  • Hi Emily,

    well, not exactly.

    Only now I'm starting to realize that I need to keep track of what telephone number is assigned to en employee depending on the office (room) where he works.

    So if Mr. Brown works both in "Office A" and "Office B", he will have two telephone number, but they must also be related to each office.

    So, probably the entry in the PhoneBookContact, in these cases, should have both foreign key (Employee and Office).

    When an employee works only in one office , the office foreign key should be null.

    If the telephone number is assigned to an office only, the employee foreign key must be null.

    Friday, December 14, 2018 9:02 AM
  • Hi liuc,

     

    Thanks for your reply.

     

    It sounds great. Hope you can finish it successfully.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, December 17, 2018 9:48 AM