How create a DRY workflow for associating phone numbers with users.

Question

Hi there,

So basically what I'm working with is a database full of phone numbers.

if I get one of those users from that phone database and they sign up to my website I want to automatically associate the number from the database that is already present, how would I go about linking those 2 tables together once a user signs up?

-Aaron

Answer 1

Assuming that you have phone number table and users table you need to store the primary keys of both table together

You can do it:

• In a user table, but then you will be able to link only one phone number to a user (and later if you will have to add more then one phone you might end up adding fields like phone_number_1, phone_number_2, etc... which is quite horrible)
• In a phone number table, but then you will be able to link only one user to a phone number (same problem may arise as in point one)
• In a separate table, but then you will have to use a join every time you need to get a user or phone number

The right choice depends on the relationships between the data that you are trying to model.

Answer 2

So, the database would be structured using composition. Keep a phone_numbers table w/ phone_number_id, the users table w/ user_id, and then a customer_phone_numbers table that maps user_ids to phone_number_ids. Then your PHP code would do something like this:

<?php

$user->save(); // save normal user data
$phoneNumbers = array();
foreach($user->getPhoneNumbers() as $phoneNumber) {
    $phoneId = getPhoneNumberId($phoneNumber); // get the phone number, or save a new record if necessary, and return that number
    $phoneNumbers[] =  $phoneId;
}

$user->savePhoneNumbers( $phoneNumbers ); // adds or removes phone numbers as necessary

---

EDIT: Based on your feedback, it doesn't sound so much like you are just looking for a way to associate the data. If I understand you correctly, the user will sign up for your website via their phone, then later register on the website. You want to combine the two registrations when this happens.

Are users creating a complete profile from their phone? Do you have a process in place to verify that the phone number provided by the user is actually theirs?

If the user is not creating a full profile via the phone, it sounds like there are two types of "profiles" being stored, a complete website profile, and a telephone profile. This way, you would have a phone profile w/ a phone_profile_id and a regular user record w/ a user_id. Then, let users verify their telephone information and associate them to a phone_profile_id using a composition table as above.

Is that a little closer?

Thanks, Joe