数据库设计 – 来自数据库中不同实体的相同数据 – 最佳实践 –
一个相当简单的问题,如果我有一个处理员工,客户和供应商的系统,所有这些系统都有多个可能的电话号码,您将如何以良好的规范化方式存储这些数字?我有一个想法,逻辑方式不是跳出来对我.
解决方法在多数情况下 . . .>“员工”总是描述人. 使用单独的员工电话号码,供应商电话号码和客户电话号码表存在严重问题. >员工可以成为客户.如果是员工 您需要实现超类型/子类型架构. (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,'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,72); insert into person_st values (2,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),party_type) on delete cascade ); insert into organization_st values (3,'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}'),phone_type) ); insert into phones values (1,'0000000000'); insert into phones values (1,'m','0000000001'); insert into phones values (3,'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,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,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 ); (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |