TLDR; What would be the best design choice for the scenario below and how would each design react at extremely large data volume?
I have experience working with large government database systems servicing 24/7 data collection, processing and reporting. It was fun to poke around all the differently designed schemas and get an idea of how all these features designed by all these different people somehow mishmashed into a working solution. Like I'm sure a few of you know, fun poking times is something of a luxury and most cycles were spent pragmatically, keeping the system alive rather than improving the design.
I have been modelling a new database system and would like to get some thoughts on how you would go about relating these entities.
Scenario: Clients, employers and practitioners require phone numbers.
We have four entities:
CLIENT
EMPLOYER
PRACTITIONER
PHONE
Phone numbers are classified by the technology used to receive calls, technology used informs data format constraints.
- Landline
- Fax
- Mobile
A description field indicates the primary usage of the phone; Home, work, etc.
Ways to relate these entities
1. Denormalize PHONE
into CLIENT
, EMPLOYER
, PRACTITIONER
Let's start with bad design and go from there. De-normalize all the phone numbers!
- 3 entities, no relationships
- Must create new columns if a new phone number is required; Leading to
CLIENT.Phone1Number
,EMPLOYER.Phone2Type
,PRACTITIONER.Phone3Description
; - or limit the number of phone numbers that can be entered for each entity
- Redundancy, lots of overhead
- Doing bulk-updates or maintenance would become tedious
Conclusion: select * from 'no_thanks';
2. Build the nth Bridge; Rename the database Pittsburgh
For each entity that can have a phone number related, create a bridge entity to bring them together.
- 7 entities, 6 relationships
- Data redundancy; PhoneNumber populated in multiple locations
- Can use bridge table if only
PhoneNumber
is required; Does not require JOIN toPHONE
- If a new entity,
FAMILYMEMBER
, requires a phone number, a new bridging table must be created
Conclusion: Maybe, depends on the difficulty of maintaining the relationships
3. A Quasi "Star Schema"
Modify PHONE
to include Foreign Keys of CLIENT.ClientID
, EMPLOYER.EmployerID
, PRACTITIONER.PractitionerID
.
- 4 entities, 3 relationships
- If one of these keys is populated, the others cannot be populated. (No case when a client, employer, practitioner would have the same phone number)
- If a new entity,
FAMILYMEMBER
, requires a phone number, a new attribute must be created inPHONE
- All FK attributes with NULL by default
Conclusion: Seems like the strongest design in my opinion