Hi,

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),
INDEX uid_index(uid)
) ENGINE=MyISAM;

CREATE TABLE contactdetails (
cid int(10) NOT NULL auto_increment,
name varchar(32) default '',
email varchar(32) default '',
UNIQUE INDEX p_index(name, email),
PRIMARY KEY(id)
) ENGINE=MyISAM;

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.

Thanks

inserts operate on only one table at a time -- if you want to insert a name and a phone number, that's two inserts

Thanks. Now that could lead to 3 queries per insert in worst case badly hitting the performance. What would be the better design in this case.

why 3 queries?

your design is fine -- you didn't mention anything about users and how users are different from contacts, but i think the 2 table structure is okay

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.

Thanks

yup

INSERT IGNORE

or

INSERT ON DUPLICATE KEY UPDATE



it is never necessary to do a SELECT first to see if it exists already

Those are MySQL specific options, and other database systems may not implement them.
SQL Server has IGNORE_DUP_KEY, with similar effects, but it is not recommended that it be used.

you are right, of course

especially given which forum this question was posted in

however, tanra was kind enough to show the table layouts, from which it is ~clearly~ evident that mysql is the database system in use here

so my suggestions are worthwhile to the original poster, if not to everyone else who might stumble across this thread

FYI for other database systems, you still don't need 3 queries, only 2

first query inserts into the first table

check the query status or completion code, and if there was a dupe, the error status will be set

if no error, carry on with the second insert

Thanks, actually I thought of that but somehow I am not getting how to avoid third query.

For example, if I use INSERT ON DUPLICATE KEY UPDATE on table contactdetails, and if it was a duplicate, how do I get the cid which I will be needing when I am inserting in contacts.

Sorry if I am missing something very obvious, a bit of elaboration will help. As you guessed, I am using MySQL

ah, with surrogate keys (auto_increments) you might need the extra SELECT once you do the INSERT (without the IGNORE) and get the error message that it duplicates the name