[ACCEPTED]-Same data from different entities in Database - Best Practice - Phone numbers example-database-normalization

In most cases . . .

• "Staff" always describes people.
• Some customers are people.
• Some customers are businesses (organizations).
• "Suppliers" are usually (always?) organizations.
• Staff can also be customers.
• Suppliers can also be customers.

There are serious problems 15 with having separate tables of staff phone 14 numbers, supplier phone numbers, and customer 13 phone numbers.

• Staff can be customers. If a staff phone number changes, does a customer phone number also need to be updated? How do you know which one to update?
• Suppliers can be customers. If a supplier's phone number changes, does a customer phone number also need to be updated? How do you know which one to update?
• You have to duplicate and maintain without error the constraints for phone numbers in every table that stores phone numbers.
• The same problems arise when a customer's phone number changes. Now you have to check to see whether staff and supplier phone numbers also need to be updated.
• To answer the question "Whose phone number is 123-456-7890?", you have to look in 'n' different tables, where 'n' is the number of different "kinds" of parties you deal with. In addition to staff, customers, and suppliers, think "contractor's phones", "prospect's phones", etc.

You need to implement a supertype/subtype 12 schema. (PostgreSQL code, not rigorously 11 tested.)

create table parties (
    party_id integer not null unique,
    party_type char(1) check (party_type in ('I', 'O')),
    party_name varchar(10) not null unique,
    primary key (party_id, party_type)
);

insert into parties values (1,'I', 'Mike');
insert into parties values (2,'I', 'Sherry');
insert into parties values (3,'O', 'Vandelay');

-- For "persons", a subtype of "parties"
create table person_st (
    party_id integer not null unique,
    party_type char(1) not null default 'I' check (party_type = 'I'),
    height_inches integer not null check (height_inches between 24 and 108),
    primary key (party_id),
    foreign key (party_id, party_type) references parties (party_id, party_type) on delete cascade
);

insert into person_st values (1, 'I', 72);
insert into person_st values (2, 'I', 60);

-- For "organizations", a subtype of "parties"
create table organization_st (
    party_id integer not null unique,
    party_type CHAR(1) not null default 'O' check (party_type = 'O'),
    ein CHAR(10), -- In US, federal Employer Identification Number
    primary key (party_id),
    foreign key (party_id, party_type) references parties (party_id, party_type) on delete cascade
);

insert into organization_st values (3, 'O', '00-0000000');

create table phones (
    party_id integer references parties (party_id) on delete cascade,
    -- Whatever you prefer to distinguish one kind of phone usage from another.
    -- I'll just use a simple 'phone_type' here, for work, home, emergency, 
    -- business, and mobile.
    phone_type char(1) not null default 'w' check 
        (phone_type in ('w', 'h', 'e', 'b', 'm')),
    -- Phone numbers in the USA are 10 chars. YMMV.
    phone_number char(10) not null check (phone_number ~ '[0-9]{10}'),
    primary key (party_id, phone_type)
);

insert into phones values (1, 'h', '0000000000');
insert into phones values (1, 'm', '0000000001');
insert into phones values (3, 'h', '0000000002');

-- Do what you need to do on your platform--triggers, rules, whatever--to make 
-- these views updatable. Client code uses the views, not the base tables.
-- In current versions of PostgreSQL, I think you'd create some "instead
-- of" rules.
--
create view people as
select t1.party_id, t1.party_name, t2.height_inches
from parties t1
inner join person_st t2 on (t1.party_id = t2.party_id);

create view organizations as 
select t1.party_id, t1.party_name, t2.ein
from parties t1
inner join organization_st t2 on (t1.party_id = t2.party_id);

create view phone_book as
select t1.party_id, t1.party_name, t2.phone_type, t2.phone_number
from parties t1
inner join phones t2 on (t1.party_id = t2.party_id);

To stretch this out a little further, a 10 table to implement "staff" needs to reference 9 the person subtype, not the party supertype. Organizations 8 can't be on staff.

create table staff (
    party_id integer primary key references person_st (party_id) on delete cascade,
    employee_number char(10) not null unique,
    first_hire_date date not null default CURRENT_DATE
);

If suppliers can only 7 be organizations, not individuals, then 6 a table implementing suppliers would reference 5 the organizations subtype in a similar way.

For 4 most companies, a customer can be either 3 a person or an organization, so a table 2 implementing customers should reference 1 the supertype.

create table customers (
    party_id integer primary key references parties (party_id) on delete cascade
    -- Other attributes of customers
);

The most straightforward way is probably 13 best. Even if a Staff, Customer, or Suppliers 12 all had a location for phone, cell phone, and 11 fax number, it it probably best to just 10 put those fields on each table.

But, the more 9 such fields you have, the more you should 8 consider some sort of "inheritance" or centralization. If 7 there is other contact information, as well 6 as multiple phone numbers, you could have 5 these common values on a centralized table, Contacts. Fields 4 specific to being a Customer, Supplier, etc., would 3 be on separate tables. The Customer table, for 2 example, would have a ContactID foreign 1 key back to Contacts.

I think the decision needs to be based on 33 a practical assessment of how important 32 this contact information is, how often it 31 changes and how much overlap there might 30 be between different types of people with 29 phone numbers.

If the contact information 28 is volatile and/or really central to the 27 application, then more normalization will 26 probably be better. This would mean having 25 a PHONE_NUMBER table that your various CUSTOMER, SUPPLIER, EMPLOYEE 24 tables (etc) could point to - or more likely 23 be referenced with some kind of three-way 22 intersection between contact type, contact 21 individual (customer/supplier/employee) and 20 contact point (phone). This way you can 19 have an employee's home phone number be 18 their customer records primary business 17 number, and if it changes, it gets changed 16 once for every usage of that contact point.

On 15 the other hand, if you're storing phone 14 numbers for the heck of it and you don't 13 use them and probably won't maintain them, then 12 spending a lot of time and effort modelling 11 and building this sophistication into your 10 database won't be worth it and you can do 9 the good, old-fashioned Phone1, Phone2, Phone3,... columns 8 on CUSTOMER, SUPPLIER, EMPLOYEE or what 7 have you. This is bad database design but 6 it is good system development practice insofar 5 as it is applying the 80/20 rule to identifying 4 project priorities.

So to sum up: If the 3 data matters, do it right, if the data doesn't 2 really matter, just slap it in - or better 1 yet, leave it out altogether.

The answer by Mike Sherrill 'Cat Recall' works on MariaDB with one single 4 change: the '~' needs to become 'LIKE'.

Here's 3 his example tested on MariaDB. I also made 2 the change asked about here in regard to types being described 1 using words rather than single chars.

create table parties (
    party_id integer not null unique,
    party_type varchar(20) not null check (party_type in ('individual', 'organization')),
    party_name varchar(50) not null unique,
    primary key (party_id, party_type)
);

insert into parties values (1,'individual', 'Mike');
insert into parties values (2,'individual', 'Sherry');
insert into parties values (3,'organization', 'Vandelay');

-- For "persons", a subtype of "parties"
create table person_st (
    party_id integer not null unique,
    party_type varchar(20) not null default 'individual' check (party_type = 'individual'),
    height_inches integer not null check (height_inches between 24 and 108),
    primary key (party_id),
    foreign key (party_id, party_type) references parties (party_id, party_type) on delete cascade
);

insert into person_st values (1, 'individual', 72);
insert into person_st values (2, 'individual', 60);

-- For "organizations", a subtype of "parties"
create table organization_st (
    party_id integer not null unique,
    party_type varchar(20) not null default 'organization' check (party_type = 'organization'),
    ein CHAR(10), -- In US, federal Employer Identification Number
    primary key (party_id),
    foreign key (party_id, party_type) references parties (party_id, party_type) on delete cascade
);

insert into organization_st values (3, 'organization', '00-0000000');

create table phones (
    party_id integer references parties (party_id) on delete cascade,
    -- Whatever you prefer to distinguish one kind of phone usage from another.
    -- I'll just use a simple 'phone_type' here, for work, home, emergency,
    -- business, and mobile.
    phone_type varchar(10) not null default 'work' check
        (phone_type in ('work', 'home', 'emergency', 'business', 'mobile')),
    -- Phone numbers in the USA are 10 chars. YMMV.
    phone_number char(10) not null check (phone_number like '[0-9]{10}'),
    primary key (party_id, phone_type)
);

insert into phones values (1, 'home', '0000000000');
insert into phones values (1, 'mobile', '0000000001');
insert into phones values (3, 'home', '0000000002');

-- Do what you need to do on your platform--triggers, rules, whatever--to make
-- these views updatable. Client code uses the views, not the base tables.
-- Inserting and Updating with Views - MariaDB Knowledge Base https://mariadb.com/kb/en/library/inserting-and-updating-with-views/
--
create view people as
select t1.party_id, t1.party_name, t2.height_inches
from parties t1
inner join person_st t2 on (t1.party_id = t2.party_id);

create view organizations as
select t1.party_id, t1.party_name, t2.ein
from parties t1
inner join organization_st t2 on (t1.party_id = t2.party_id);

create view phone_book as
select t1.party_id, t1.party_name, t2.phone_type, t2.phone_number
from parties t1
inner join phones t2 on (t1.party_id = t2.party_id);