How to properly index a table two other tables have a one-to-many relationship to?

Question

Imagine I have three tables, called "customers", "companies" and "phone_numbers". Both customers and companies can have multiple phone numbers. What would be the best way to index phone_numbers? Have both customer_id and company_id and keep one of them null? What if there are more than two tables with a one-to-many relationship with phone_numbers?

Answer 1

Your business rules might only state one-to-many, but in reality people & companies can be a many-to-many relationship. One person can have many phone numbers (home, cell, etc), and a phone number can relate to many people (myself, my significant other, etc). Likewise, a company number and my business number can be the same - you just use an extension number to reach me directly.

Indexing the foreign keys would be a good idea, but beware of premature optimization. Depending on setup, I'd consider a unique constraint on the phone number column but I would not have the phone number column itself as a primary key.

Answer 2

I would go with identity columns in the customer and company tables, then in the phone number table do as you said and keep one null and the other populated. I do something similar to this and it works out fine as long as you validate data so that it doesn't go in with both values being null. For a more elegant solution you could have two columns: one that is an id, and another that is a type identifier. Say 1 for customers and 2 for companies, that way you don't have to worry about null data or a lot of extra columns.

Answer 3

I'd add two columns to the phone_numbers table. The first would be an index that tells you what table to associate with (say, 1 = customers and 2 = companies). The second would be the foreign key to the appropriate table.

This way you can add as many phone number sources as you want.

If a particular person or company has more than one phone number, there would be multiple rows in the phone_numbers table.

Answer 4

The closest thing I have to a pattern is the following -- any two entities with a many-to-many relationship require an associative entity (a cross-reference table) between them, like so (surrogate keys assumed):

CREATE TABLE CUSTOMER_XREF_PHONE
( CUSTOMER_ID      NUMBER NOT NULL,
  PHONE_NUMBER_ID  NUMBER NOT NULL,
  CONSTRAINT       CUSTOMER_XREF_PHONE_PK 
    PRIMARY KEY      (CUSTOMER_ID, PHONE_NUMBER_ID),
  CONSTRAINT       CUSTOMER_XREF_PHONE_UK 
    UNIQUE           (PHONE_NUMBER_ID, CUSTOMER_ID),
  CONSTRAINT       CUSTOMER_XREF_PHONE_FK01
    FOREIGN KEY      (CUSTOMER_ID)
      REFERENCES       CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE,
  CONSTRAINT       CUSTOMER_XREF_PHONE_FK02
    FOREIGN_KEY      (PHONE_NUMBER_ID)
      REFERENCES       PHONE_NUMBERS (PHONE_NUMBER_ID) ON DELETE CASCADE
);

Such an implementation pattern can:

• Be fully protected by database-level referential integrity constraints

• Support bi-directional access (sometimes you need to see who else has that phone number)

• Be self-cleaning if your database supports ON DELETE CASCADE

• Be extended through the use of a "relationship type" attribute to map multiple independent relationships between the entities, such as:

  • customer has a home telephone number
  • customer has a daytime telephone number
  • customer has a fax telephone number
  • customer has a mobile telephone number