How to set up a customer table with multiple phone numbers? - Relational database

Question

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

A client may have several phone numbers, for example. Cell, Work, etc. The phoneID in the Customer table is unique and points to the PhoneID in the Phone table. If the user record is deleted, you must also delete the phone identifier in the telephone table.

Do you have any problems in my design? Is it designed correctly? My phoneID problem in the Customer table is a child, and if the child entry is deleted, then i cannot automatically delete the parent (phone) entry.

Answer 1

mrjoltcola , .

, :

1) , CASCADE DELETE - SQL

2) , , ( , . )

3) - - , () ,

Answer 2

, . Phone + PhoneDetail. .

1) . . , , , (Work, Home, Mobile, Fax, Emergency). - , . , (Customer.Fax).

downsides: , - kludgy. SQL "select * from customer where cell = ? or home = ? or work = ? or emergency = ?". . , , .

2) . . , DDL-. → CustomerPhone

Customer (
   customerId
)

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

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