1

Hi I have the following database design problem I need to resolve.

A section employs many staff members

Each section has multiple phone numbers

Each staff member has their own internal telephone number.

I have created three entities: Phone, Staff and Section. The phone entity store the numbers with the phone number as the PK. However, the ERD I have created has a loop between the three entities. Any simple solutions to avoid the loop using the phone entity?

Phone Loop ERD Image

Mureinik
  • 297,002
  • 52
  • 306
  • 350
Ableman
  • 29
  • 9
  • Why have a sectionNumberFK in Phone? Just have the Section entity have a reference to phoneNumber like Staff does. – Tah Oct 09 '16 at 00:06
  • Would it be okay to remove the relationship between Section and Staff and use phone number attribute to find the section number attribute as each phone number is assigned a section? – Ableman Oct 09 '16 at 00:12
  • Because each section has multiple numbers and I cannot have multivalued attributes in the ERD because it must be in 3NF. – Ableman Oct 09 '16 at 00:13
  • That would complicate it. It would make for bad business logic to remove the relationship between Section and Staff. Why shouldn't Staff "know" what their Section is and why shouldn't "Section" know its Staff? To reach 3NF for this diagram, you may need to create a lookup table for the Section to Phone relationship – Tah Oct 09 '16 at 00:17
  • Hmm yes good point, that's why I wanted Section linked directly to staff. Do you think my current erd presents any issues? Do you think there is actually a loop? – Ableman Oct 09 '16 at 00:40
  • There's an issue currently because SectionNumberId could be null in Phone if it's only associated to Staff, moving it to a lookup table (SectionNumberId and Phone) will clean it up. Normally wouldn't suggest a lookup table, but this avoids the null possibility – Tah Oct 09 '16 at 00:44

2 Answers2

1

My understanding of an ERD loop is different from yours. In my understanding, an ERD loop is when the foreign key references go around and around endlessly. In your case, there would be a loop if Section had an FK reference to staff. Then section references staff, staff references phone, and phone references section, in a loop.

You haven't modeled it that way, because that would be incorrect in your case. You have correctly modeled it the other way, with Staff referencing Section.

So you don't really have a loop. Staff references both phone and section, but those are independent relationships.

So why is it still a problem?

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
  • Hi, thanks for the feedback. Yes that's what I thought but some others I am working on this project with were unsure if the ERD in the image presented a loop. Thanks for clearing that up. – Ableman Oct 09 '16 at 11:50
0

You can most likely remove the relationship between Phone and Section. If each Phone is only answered by one Staff member, you have already resolved your issue. You know which Phones are in a Section based on the Staff member who answers that Phone.

If a Phone can be answered by more than one Staff member, then you need an associative table to represent that many-to-many relationship.

If you still need to keep track a primary Phone for each Staff member, you can add an "IsPrimary" Flag to the associative table to indicate that a particular Phone is the primary for a particular user. In this case you will want to have a unique constraint to the associative table for at least the Staff member reference to ensure that a Staff member does not have more than one primary Phone.

You may also want to add a unique constraint to ensure that a Phone cannot be the primary for more than one Staff member.

Update to Handle Two Phone Entities

Even if you decide to have two phone entities, you'll most likely want to ensure that a phone number is unique across both entities. You can do this by implementing mutually exclusive subtypes. A good explanation of how to do that can be found here: How to Implement Referential Integrity in Subtypes

Once you have the subtypes, you could use your original model. SectionPhone would be one of the subtypes and would be what you currently have in your Phone entity. StaffPhone would be the other subtype which would be referenced by the Staff table.

Community
  • 1
  • 1
Bobby
  • 88
  • 5
  • Hi thanks the assumption is that the assigned section phone numbers (which can be many per section) are able to be shared among all staff i.e. some numbers that are not specific to any staff member. This allows for staff to share a desk telephone on different days and times. Also every staff must have a unique phone number that is not part of the shared section telephone numbers. – Ableman Oct 09 '16 at 01:18
  • Are the phone numbers assigned to a single physical phone? This appears to be the case based on your phone entity. – Bobby Oct 09 '16 at 01:32
  • I need to reflect multiple phones per section. Like the call centre phones. And staff internal phones are direct phones to particular staff member. – Ableman Oct 09 '16 at 02:30
  • I think the simplest solution is to have two phone entities one being staffphone and the other being sectionphone, neither of which is related. Surely there is a way to use a single phone entity? – Ableman Oct 09 '16 at 02:46
  • Hi cheers for the suggestion. Good point I forgot about having unique phone numbers between the two entities. I guess that wouldn't be an issue though in this case as it is comparable to accidently entering in a customers information in a staff entity, it can happen but ofcourse you want to avoid it. I guess I need to consider what option does what I need with the least amount of problems. I know supertype/subtype would resolve this challenge but am restricted in being able to use it. – Ableman Oct 09 '16 at 04:15
  • Why are you restricted from being able to use supertype/subtype? – Bobby Oct 09 '16 at 12:35