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
phoneTypeI开发者_如何学GoD ...
phoneNumber ...
.
.
.
);

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

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.

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.

I think you've overdesigned it. I see no use for a separate Phone + PhoneDetail table. Typically there are two practical approaches.

1) Simplicity -Put all of the phones in the Customer record itself. Yes, it breaks normalization rules, but its very simple in practice and usually works as long as you provide (Work, Home, Mobile, Fax, Emergency). Upside is code is simply to write, time to implementation is shorter. Retrieving all the phones with a customer record is simple, and so is using a specific type of phone (Customer.Fax).

The downsides : adding additional phone types later is a little more painful, and searching for phone numbers is kludgy. You have to write SQL like "select * from customer where cell = ? or home = ? or work = ? or emergency = ?". Assess your design up front. If either of these issues is a concern, or you don't know if it may be a concern, go with the normalized approach.

2) Extensibility - Go the route you are going. Phone types can be added later, no DDL changes. Customer -> CustomerPhone

Customer (
   customerId
)

CustomerPhone (
   customerId references Customer(customerId)
   phoneType references PhoneTypes(phoneTypeId)
   phoneNumber
)

PhoneTypes (
   phoneTypeId   (H, W, M, F, etc.)
   phoneTypeDescription
)

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