数据库中不同实体的相同数据 - 最佳实践 - 电话号码示例

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

最直接的方法可能是最好的。即使员工、客户或供应商都有电话、手机和传真号码的位置，最好将这些字段放在每个表上。

但是，这样的字段越多，你就越应该考虑某种“继承”或集中化。如果有其他联系信息以及多个电话号码，您可以在集中表“联系人”中包含这些通用值。联系人。特定于客户、供应商等的字段将位于单独的表中。例如，“客户”表将有一个返回“联系人”的 ContactID 外键。

Answer 3

我认为这个决定需要基于对这些联系信息的重要性、变化频率以及拥有电话号码的不同类型的人之间可能有多少重叠的实际评估。

如果联系信息不稳定和/或对于应用程序来说确实很重要，那么更多的标准化可能会更好。这意味着拥有一个 PHONE_NUMBER 表，您的各种 CUSTOMER、SUPPLIER、EMPLOYEE 表（等）可以指向该表 - 或者更有可能通过联系人类型、联系人个人（客户/供应商/员工）和联系人之间的某种三向交集来引用联络点（电话）。通过这种方式，您可以将员工的家庭电话号码作为其客户记录的主要业务号码，如果发生变化，则每次使用该联系点时都会更改一次。

另一方面，如果您存储电话号码只是为了它，并且您不使用它们并且可能不会维护它们，那么花费大量时间和精力进行建模并将这种复杂性构建到您的数据库中是不会的。不值得，您可以在“客户”、“供应商”、“员工”或您拥有的其他内容上使用老式的“电话 1”、“电话 2”、“电话 3”... 列。这是糟糕的数据库设计，但它是良好的系统开发实践，因为它应用 80/20 规则来确定项目优先级。

总结一下：如果数据很重要，就做对，如果数据并不重要，就直接把它放进去——或者更好的是，干脆把它去掉。

Answer 4

答案，作者：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);