Rails / postgres: what is the best way to store a list of phone numbers for a single user

Question

So, I'm a little confused about how to store a list of phone numbers in one column. Here are the requirements:

• Users can have more than one phone number.
• Phone numbers must be unique, so if user A added the phone number used by user B, then a verification error should be displayed.
• If the user has several phone numbers, you should select the default phone number.
• The solution should be compatible with Postgresql.

I came up with four possible solutions:

• HStore: create a phone_number field that stores the hash of all phone numbers. e.g. {1=>"[phone]", 2=>"9237492734", "default"=>1} . In this case, I need to make many requests to make sure that the new phone number is unique, for example, I need to request User.where("phone_number @> ('1' => '[phone]')") , then check 2 User.where("phone_number @> ('2' => '[phone]')") ... etc.
• An array of phone numbers in one field: phone_number will store numbers with commas, for example, "+ 1-800-123-1234,9237492734". Checking an existing phone number will be easy User.where("phone_number LIKE '%[phone]%'") , but it takes a long time to get the database. default_phone will be added to the table, and also make the first phone number the default default.
• Limiting phone numbers to 3 (which is enough) and creating the phone_number_1 , phone_number_2 and phone_number_3 . Checking the uniqueness of a phone number will require 3 queries. You will also need to add default_phone .
• Adding a new table phone_numbers (id: integer, user_id: integer, phone_number: string, default: boolean) and establishing a has_many relationship with the user model. Which doesn't really seduce ... create an entire table for 1 field. But it has a quick search and will have unlimited phone numbers for each user.

Any ideas, tips and suggestions are welcome.

Answer 1

Two tables are the solution. You can potentially have multiple users to whom you can get the same phone number, for example a work number or a home number that are landline.

 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 "each person has a unique phone number and this phone number cannot be used to contact anyone else," just add the UNIQUE constraint to the collection_number column.