Rails/postgres: What is the best way to store list of phone numbers for one user

Question

So I am little confused on how to store a list of phone numbers in one column. Here are the requirements:

1. Users can have more than one phone number.
2. Phone numbers have to be unique, so if user A added a phone number that is used by user B then a validation error should be displayed.
3. A default phone number should be selected if the user have more than one phone number.
4. Solution have to be compatible with Postgresql.

I thought up of four possible solutions:

1. HStore: making a phone_number field that stores a hash of all the phone numbers. e.g. {1=>"[phone]", 2=>"9237492734", "default"=>1}. In this case I need to make lot of queries to make sure that a new phone number is unique, for example I need to query User.where("phone_number @> ('1' => '[phone]')") then check in 2 User.where("phone_number @> ('2' => '[phone]')") ... etc.
2. Array of phone numbers in one field: phone_number will store comma separated phone numbers like "+1-800-123-1234,9237492734". Checking a existing phone number would be easy User.where("phone_number LIKE '%[phone]%'") but will take a lot of time for the database to pick it up. default_phone will be added to the table as well or making the first phone number as the default one by convention.
3. Limiting phone numbers to 3 (which is enough) and creating phone_number_1, phone_number_2 and phone_number_3 fields. Checking the uniqueness of a phone number will consume 3 queries. Also will require adding default_phone.
4. Adding a new table phone_numbers (id:integer, user_id:integer, phone_number:string, default:boolean) and setting has_many relationship with User model. Which is not really seducing ... create a whole table for 1 field. But it have fast lookups and will have limitless phone numbers for each user.

Any ideas, hints and suggestions are greatly appreciated.

Answer 1

Two tables is the solution to go after. You can potentially have multiple users who can be reached at the same phone number, such as a work number or home number that are landlines.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  -- other bits of information
);

CREATE TABLE phone_numbers (
  user_id INTEGER REFERENCES users (id),
  phone_number TEXT NOT NULL,
  location TEXT NOT NULL, -- Mobile, home, work
  PRIMARY KEY (user_id, phone_number),
  INDEX (phone_number)
);

If you really want to enforce the 'each person has a unique phone number and that phone number cannot be used to contact anyone else', just add a UNIQUE constraint to the phone_number column.