Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.

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

This question came from our site for professional and enthusiast programmers.

1

I've recently solve similar problem. In my case, clients where persons and companies, and suppliers too, persons and companies. I managed to establish a hierarchy contacts. So, if you want to contact a supplier, you always look for work or mobile phone, but first you select an area within a hierarchy of this supplier (management, stock, accountant, etc.) Also, employees have work phone too (and extension numbers)
– HoraciuxJun 28 '14 at 15:20

1 Answer
1

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:

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:

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: