来自数据库中不同实体的相同数据 - 最佳实践 - 电话号码示例 [英] Same data from different entities in Database - Best Practice - Phone numbers example

问题描述

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

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.

推荐答案

在大多数情况下...

• 员工"总是描述人.
• 有些客户是人.
• 有些客户是企业(组织).
• 供应商"通常(总是?)组织.
• 员工也可以是客户.
• 供应商也可以是客户.

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

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

• 员工可以是客户.如果一个工作人员电话号码变了，有客户吗电话号码也需要更新吗?你怎么知道要更新哪一个?
• 供应商可以是客户.如果一个供应商的电话号码发生变化，客户是否电话号码也需要更新吗?你怎么知道要更新哪一个?
• 你必须无误地复制和维护约束对于每张表中的电话号码存储电话号码.
• 同样的问题出现在客户的电话号码更改.现在你必须检查一下员工和供应商电话号码也需要更新.
• 回答谁的电话号码是 123-456-7890?"，你必须查看n"个不同的表，其中'n' 是不同的数量你打交道的种类"政党.在除了员工、客户和供应商，认为承包商的电话"、潜在客户的电话"等

• 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.

您需要实现一个超类型/子类型架构.(PostgreSQL 代码，未经严格测试.)

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

为了进一步扩展这一点，实现staff"的表需要引用 person 子类型，而不是 party 超类型.组织不能有员工.

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

这篇关于来自数据库中不同实体的相同数据 - 最佳实践 - 电话号码示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！