在数据库中来自不同实体的相同数据 - 最佳实践 - 电话号码示例
在数据库中,相同的数据可能会在不同的实体中出现,如电话号码。这种情况下,我们需要考虑如何处理这些数据,以及如何解决可能出现的问题。
出现这个问题的原因主要是因为我们需要根据实际情况来评估联系信息的重要性、变化频率以及不同实体之间的重叠情况。如果联系信息非常重要且容易变化,并且对应用程序来说非常关键,那么更多的规范化可能会更好。这意味着我们可以创建一个PHONE_NUMBER表,各种实体(如CUSTOMER、SUPPLIER、EMPLOYEE等)可以引用或使用联系点(电话号码)的联系类型和联系个体(顾客/供应商/员工)进行关联。这样一来,员工的家庭电话号码可以成为他们顾客记录的主要业务号码,如果号码变化了,只需在使用该联系点的每个地方更新一次即可。
另一方面,如果我们只是随便存储电话号码,并且不会使用或维护它们,那么在数据库中花费大量时间和精力来建模和构建这种复杂性可能是不值得的。在这种情况下,可以在CUSTOMER、SUPPLIER、EMPLOYEE等表中使用传统的Phone1、Phone2、Phone3等列。虽然这种设计不符合数据库规范,但从系统开发实践的角度来看,这是应用80/20法则来确定项目优先级的好方法。
总而言之:如果数据很重要,那就做得正确;如果数据并不那么重要,可以随便处理,或者更好的做法是干脆不存储这些数据。
问题的出现原因是当数据库中的不同实体(Staff,Customer,Suppliers)都有电话号码、手机和传真号码的位置时,直接在每个表上放置这些字段可能是最直接的方法。然而,随着这些字段的增加,我们应该考虑一些"继承"或集中化的方法。如果还有其他联系信息以及多个电话号码,可以将这些公共值放在一个中央表Contacts上。每个实体(Customer,Supplier等)的特定字段可以放在单独的表中。例如,Customer表将有一个指向Contacts的ContactID外键。
要解决这个问题,我们可以采用一种继承或集中化的方法。首先,在数据库中创建一个中央表Contacts,该表包含公共的联系信息字段(例如姓名、地址等)。然后,每个实体(Staff,Customer,Suppliers)的表将包含一个指向Contacts表的外键(例如ContactID)。这样,每个实体都可以访问和使用Contacts表中的公共联系信息。这种方法可以避免在每个实体表中重复存储相同的联系信息。
以下是通过SQL代码实现这种解决方法的示例:
CREATE TABLE Contacts ( ContactID INT PRIMARY KEY, Name VARCHAR(50), Address VARCHAR(100), -- 其他公共联系信息字段 ); CREATE TABLE Staff ( StaffID INT PRIMARY KEY, ContactID INT, -- 其他Staff特定字段 FOREIGN KEY (ContactID) REFERENCES Contacts(ContactID) ); CREATE TABLE Customer ( CustomerID INT PRIMARY KEY, ContactID INT, -- 其他Customer特定字段 FOREIGN KEY (ContactID) REFERENCES Contacts(ContactID) ); CREATE TABLE Suppliers ( SupplierID INT PRIMARY KEY, ContactID INT, -- 其他Supplier特定字段 FOREIGN KEY (ContactID) REFERENCES Contacts(ContactID) );
从上述内容中可以得出以下结论:
问题的原因:
- 员工可以是顾客,供应商也可以是顾客,导致电话号码的更新和查找变得复杂。
- 需要在每个存储电话号码的表中重复维护约束。
- 需要在多个不同的表中查找电话号码的所有者。
解决方法:
- 实施超类型/子类型模式。
- 创建一个名为"parties"的表,用于存储所有类型的实体。
- 创建"person_st"和"organization_st"表作为"parties"表的子类型。
- 创建"phones"表用于存储电话号码,并引用"parties"表的主键。
- 使用视图来提供可更新的接口,以便客户端代码可以使用视图而不是基本表。
代码示例:
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) ); 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 ); 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), primary key (party_id), foreign key (party_id, party_type) references parties (party_id, party_type) on delete cascade ); create table phones ( party_id integer references parties (party_id) on delete cascade, phone_type char(1) not null default 'w' check (phone_type in ('w', 'h', 'e', 'b', 'm')), phone_number char(10) not null check (phone_number ~ '[0-9]{10}'), primary key (party_id, phone_type) ); 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 );
关于使用虚拟列作为子类型表中的类型列的想法:
- 不能将虚拟列用作外键,但可以使用生成的存储列。
- 可以在子类型表中使用生成的存储列来强制执行单一类型,而无需显式提供类型。