Mysql – How to design Address table with different types

Sorry, but is an entrepreneur not also an individual? You should record the name etc. in individual, then the additional info in another table. Now you may have one person double. And stuff like address should be linked to "entity" - not even client, you may end up having suppliers there too.

The "Data Model Resource Book" has perfect diagrams for address and contact handling including relationships of entities with each other. There is a lot of detail issues you get seriously wrong in your approach.

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:

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.