如何设置多个电话号码的客户表？ - 关系数据库设计 [英] How to Set Customer Table with Multiple Phone Numbers? - Relational Database Design

问题描述

CREATE TABLE Phone
(
phoneID - PK
.
.
.
);

CREATE TABLE PhoneDetail
(
phoneDetailID - PK
phoneID - FK points to Phone
phoneTypeID ...
phoneNumber ...
.
.
.
);

CREATE TABLE Customer
(
customerID - PK
firstName
phoneID - Unique FK points to Phone
.
.
.
);

客户可以有多个电话号码，单元格，工作等。
客户表中的phoneID是唯一的，并指向Phone表中的PhoneID。
如果客户记录被删除，电话表中的phoneID也应该被删除。

A customer can have multiple phone numbers e.g. Cell, Work, etc. phoneID in Customer table is unique and points to PhoneID in Phone table. If customer record is deleted, phoneID in Phone table should also be deleted.

您对我的设计有任何疑问吗？这是否正确设计？我的问题是
在Customer表中的phoneID是一个孩子，如果子记录被删除，则i
不能自动删除父（电话）记录。

Do you have any concerns on my design? Is this designed properly? My problem is phoneID in Customer table is a child and if child record is deleted then i can not delete the parent (Phone) record automatically.

推荐答案

由于mrjoltcola已经解决了规范化问题，我将解决在电话中有记录并且没有电话详细记录的问题。

As mrjoltcola already addressed the normalization, I'll tackle the problem of having a record in phone and no record in phone detail.

如果这是你唯一的问题，有三种方法：

If that is your only problem there are three approaches:

1）不要从详细信息表中删除，从电话与CASCADE DELETE - 使用单个SQL语句从两个表中删除并保持数据一致

1) do not delete from detail table but from phone with CASCADE DELETE - gives a delete from two tables with single SQL statement and keeps data consistent

2）在细节表上有触发器，将自动删除父父进程的最后一个记录从子进程中删除（这将不会很好地执行并且会减慢表上的所有删除操作，而且很丑陋，仍然可以这样做）

2) have triggers on the detail table that will delete the parent automatically when last record for a parent is deleted from the child (this will not perform well and will slow down all deletes on the table. and it is ugly. still it is possible to do it)

3）在应用程序的业务逻辑层中执行此操作 - 如果此层正确分隔，并且如果用户（应用程序）将仅通过此层修改数据，则可以达到所需的一致性保证级别

3) do it in the business logic layer of the application - if this layer is properly separated and if users(applications) will be modifying data only through this layer you might reach desired level of consistency guarantee

这篇关于如何设置多个电话号码的客户表？ - 关系数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！