Best relationship database design to manage types for different entities

database-design

Suppose we have to manage phone types for: Clients, Employees and Suppliers.
Each of these can have multiple phone numbers, so for better management I would like to add phone type.
Each entity can have different phone types like:

  • Clients: Home, work , mobile
  • Employees: Home, mobile
  • Suppliers: Stock, Director, and so on.

Problem: I want to be able to show for clients only the possible client phone types, for suppliers only possible supplier phone types and so on…

Question:
What will be the best relationship database design to manage this kind of data?

Case 1 : To make one table PhoneType and then ClientsPossiblePhoneTypes, SupplierPossiblePhoneTypes , EmployeesPossiblePhoneTypes, or

Case2: to make table ClientPhoneType, EmployeePhonetype, SupplierPhoneType.

Best Answer

If your various types of individuals and organizations (employees, clients, suppliers) each have multiple phone numbers of various types, then you want to keep the phone numbers in a child table.

This child table should be an intersection between the individual/organization and a phone type table.

Since you have different allowable phone types for each type of legal entity, you can create a table for each of these lists of allowable phone types (your Case 1). The only issue with this is that you may have duplication of data, for example having a "mobile" record in multiple allowable types tables. There is a way to have distinct lists of allowable phone types while at the same time rationalizing these lists for management purposes.

Consider this ERD:

ERD

There is one master lists of phone types (PHONE_TYPE) which includes flags for each possible legal entity type, indicating whether a particular phone type is allowable for each type of legal entity. The table might look like this:

create table PHONE_TYPE
( phone_type_id int primary key
, description varchar(10)
, is_client_type bit
, is_employee_type bit
, is_supplier_type bit
);

From this master list of phone types, you can create distinct views for each legal entity phone type list (i.e. CLIENT_PHONE_TYPE, EMPLOYEE_PHONE_TYPE, SUPPLIER_PHONE_TYPE). One of these views might look like this:

create view CLIENT_PHONE_TYPE
as
select * from PHONE_TYPE
where is_client_type = 1;

This lets you maintain the phone types master list in one place while enforcing the list of allowable phone types for each of the different types of legal entity that you support.

Related Question