I need some help in designing the schema and queries for phonebook. I plan to store phone book in two tables, one table will store user id & phone numbers and other table will store the details associated with that phone number (name, email etc) referenced by cid. I chose this design because a contact can have multiple phone number (home, work, mobile) and I do not want to repeat all the common information (like name, email). Please suggest if my assumption is wrong and there are better way to design phone book. We have over million users and each user can have 500-1000 contacts. Following are the sample table definition.
CREATE TABLE contacts (
uid int(10) unsigned NOT NULL,
phone varchar(16) default '',
cid int(10) unsigned not NULL,
UNIQUE INDEX p_index(uid, phone),
CREATE TABLE contactdetails (
cid int(10) NOT NULL auto_increment,
name varchar(32) default '',
email varchar(32) default '',
UNIQUE INDEX p_index(name, email),
One of the key problem with this design is how to insert a new contact in single query. For example, I like to enter a new contact "John" with phone number 18885551111. So with this design, I have to first insert the name in contactdetails table, get inserted id and use it with second query to insert phone number in contacts table. I feel that's going to be a performance issue when we handle million of users.
Is there a way I can do it in single query. Or can you suggest better design for this kind of application.
See, I need to first insert names, if it's duplicate, I need to query it's index and then insert into phone number table, so it will be three queries. If I first do select to check if name exist and if not insert, in that case also it will be three queries. Or am I missing anything?:
User and contacts are different in a sensesay user owns phone and contacts are in his pocketbook.