Same data from different entities in Database - Best Practice - Phone numbers example

Question

Given a database system which deals with Staff, Customers and Suppliers, all of which have multiple possible phone numbers, how would you go about storing these numbers in a nice normalised way? I have a had a little think about and the logical way isn't jumping out at me.

Answer 1

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

Here's his example tested on MariaDB. I also made the change asked about here in regard to types being described 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);

Answer 2

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 with having separate tables of staff phone numbers, supplier phone numbers, and customer 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 schema. (PostgreSQL code, not rigorously 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 table to implement "staff" needs to reference the person subtype, not the party supertype. Organizations 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 be organizations, not individuals, then a table implementing suppliers would reference the organizations subtype in a similar way.

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

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

Answer 3

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

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

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

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

Answer 4

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

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