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

Question

给定一个处理员工、客户和供应商的数据库系统，所有这些都有多个可能的电话号码，您将如何以一种很好的规范化方式存储这些号码？我想了一下，合乎逻辑的方法并没有让我跳出来。

Answer 1

大多数情况下。 . .

“员工”总是描述人。

有些客户是人。

一些客户是企业
(组织)。

“供应商”通常(总是？)
组织。

员工也可以是客户。

供应商也可以是客户。

使用单独的员工电话号码、供应商电话号码和客户电话号码表存在严重问题。

员工可以是客户。如果一个工作人员
电话号码变了，有客户吗
电话号码也需要更新吗？你怎么知道更新哪一个？

供应商可以是客户。如果一个
供应商的电话号码发生变化，客户是否
电话号码也需要更新吗？你怎么知道更新哪一个？

你必须无误地复制和维护约束
对于每张表中的电话号码
存储电话号码。

出现同样的问题
客户的电话号码更改。现在
你必须检查一下
员工和供应商电话号码
也需要更新。

回答问题“谁的电话
号码是 123-456-7890？”，你必须
查看“n”个不同的表，其中
'n' 是不同的数量
你打交道的“种类”政党。在
除了员工、客户和
供应商，认为“承包商的
电话”、“潜在客户的电话”等

您需要实现一个父类(super class)型/子类型架构。 (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);

为了进一步扩展，实现“员工”的表需要引用人员子类型，而不是聚会父类(super class)型。组织不能有员工。

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

如果供应商只能是组织，而不是个人，那么实现供应商的表将以类似的方式引用组织子类型。

对于大多数公司，客户可以是个人或组织，因此实现客户的表应该引用父类(super class)型。

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