Одни и те же данные из разных объектов в базе данных - Best Practice - Пример телефонных номеров

Question

Довольно простой вопрос: если бы у меня была система, которая бы работала с персоналом, клиентами и поставщиками, у каждого из которых было несколько возможных телефонных номеров, как бы вы сохранили эти номера в хорошем нормированном виде? Я немного подумал, и логичный путь не выпрыгивает из меня.

Answer 1

В большинстве случаев.,,

• "Персонал" всегда описывает людей.
• Некоторые клиенты люди.
• Некоторые клиенты являются предприятиями (организациями).
• "Поставщиками" обычно являются (всегда?) Организации.
• Персонал также может быть клиентом.
• Поставщики также могут быть клиентами.

Существуют серьезные проблемы с наличием отдельных таблиц телефонных номеров сотрудников, телефонных номеров поставщиков и телефонных номеров клиентов.

• Сотрудники могут быть клиентами. Если телефонный номер персонала меняется, необходимо ли обновлять номер телефона клиента? Как узнать, какой из них обновить?
• Поставщики могут быть клиентами. Если номер телефона поставщика изменяется, необходимо ли также обновлять номер телефона клиента? Как узнать, какой из них обновить?
• Вы должны дублировать и поддерживать без ошибок ограничения для телефонных номеров в каждой таблице, в которой хранятся телефонные номера.
• Те же проблемы возникают при изменении номера телефона клиента. Теперь вам нужно проверить, нужно ли обновлять номера телефонов сотрудников и поставщиков.
• Чтобы ответить на вопрос "Чей номер телефона 123-456-7890?", Вам нужно посмотреть в "n" разных таблицах, где "n" - это количество разных "видов" партий, с которыми вы имеете дело. В дополнение к персоналу, клиентам и поставщикам, подумайте о "телефонах подрядчика", "телефонах потенциальных клиентов" и т. Д.

Вам необходимо реализовать схему супертипа / подтипа. (Код PostgreSQL, строго не проверенный.)

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);

Чтобы продвинуть это немного дальше, таблица для реализации "персонала" должна ссылаться на подтип человека, а не на супертип партии. Организации не могут быть в штате.

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
);

Если поставщиками могут быть только организации, а не отдельные лица, то таблица, в которой реализованы поставщики, будет аналогичным образом ссылаться на подтип организаций.

Для большинства компаний заказчиком может быть либо физическое лицо, либо организация, поэтому таблица, в которой реализованы клиенты, должна ссылаться на супертип.

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

Answer 2

Я думаю, что решение должно основываться на практической оценке того, насколько важна эта контактная информация, как часто она меняется и насколько часто могут совпадать люди разных типов с телефонными номерами.

Если контактная информация нестабильна и / или действительно имеет центральное значение для приложения, то, скорее всего, будет лучше нормализация. Это будет означать наличие таблицы PHONE_NUMBER, на которую могут указывать ваши различные таблицы CUSTOMER, SUPPLIER, EMPLOYEE (и т. Д.) Или, что более вероятно, с каким-либо трехсторонним пересечением между типом контакта, контактным лицом (клиент / поставщик / сотрудник) и контактная точка (телефон). Таким образом, домашний телефон сотрудника может быть основным деловым номером их клиентов, и если он меняется, он меняется один раз для каждого использования этой контактной точки.

С другой стороны, если вы храните телефонные номера, и вы не используете их и, вероятно, не будете их обслуживать, то тратите много времени и усилий на моделирование и внедрение этой сложности в свою базу данных, и вы выиграете ". это того не стоит, и вы можете сделать хорошие, старомодные столбцы Phone1, Phone2, Phone3,... на CUSTOMER, SUPPLIER, EMPLOYEE или что у вас есть. Это плохой дизайн базы данных, но это хорошая практика разработки системы, поскольку она применяет правило 80/20 для определения приоритетов проекта.

Итак, подведем итог: если данные имеют значение, сделайте это правильно, если данные на самом деле не имеют значения, просто добавьте их - или еще лучше, оставьте их вообще.

Answer 3

Ответ Mike Sherrill 'Cat Recall' работает на MariaDB с одним единственным изменением: "~" должно стать "LIKE".

Вот его пример, проверенный на MariaDB. Я также внес изменения, о которых здесь просили, в отношении типов, описываемых с использованием слов, а не одиночных символов.

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 4

Самый простой способ, вероятно, лучший. Даже если у всех сотрудников, клиентов или поставщиков есть место для телефона, мобильного телефона и номера факса, лучше всего просто поместить эти поля в каждую таблицу.

Но чем больше таких полей у вас есть, тем больше вы должны рассматривать своего рода "наследование" или централизацию. Если есть другая контактная информация, а также несколько телефонных номеров, вы можете использовать эти общие значения в централизованной таблице " Контакты". Поля, относящиеся к типу "Клиент", "Поставщик" и т. Д., Будут находиться в отдельных таблицах. Например, таблица Customer будет иметь внешний ключ ContactID для контактов.