Relational schema design: "two-to-one" relationship or "backward delete cascade"

Question

Suppose I have the following schema (just an example) where each customer has exactly two phone numbers with distinct purposes:

create table customer (
    id integer primary key,
    home_phone_id integer not null,
    work_phone_id integer not null,
    foreign key (home_phone_id) references phone (id),
    foreign key (work_phone_id) references phone (id)
);

create table phone (
    id integer primary key,
    number varchar(10) not null
);

The foreign keys are placed in the customer table in order to distinguish between the home and work phone numbers. This technically makes phone the "parent" table and customer the child, even though a customer "has" a phone number, not the other way around. (The application would never query for a phone number without its customer.)

When a customer row is deleted, I want its two rows from phone to be automatically deleted. This is a cascade from a "child" to a "parent", not from parent to child as is normal. My impression is that no SQL database supports this behavior. I could use a trigger to accomplish this, but I think the fact that it's not supported means I'm designing the schema wrong.

I can imagine two alternative designs, neither of which seems very good:

1. Create identical home_phone and work_phone tables, each with a customer_id, making customer the parent.

2. Make customer the parent by moving the foreign key to phone, and use an extra column in phone (like a boolean is_home) to distinguish between the two types of phone numbers.

What's the right way to design this?

Answer 1

Option 2 is going to be your best bet. By moving the customer_id to the phone table, and adding a new phone_type field, you gain two immediate advantages.

1. You now have the CASCADE DELETE that you are looking for.
2. This also becomes extendable to adding more phone types in the future. Perhaps you want to add mobile, secretary, or assistant phone number types.

The actual data in the phone_type field can be a standardized list of values, such as: home, work, cell.