Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2011
    Posts
    10

    Phonebook database design

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    inserts operate on only one table at a time -- if you want to insert a name and a phone number, that's two inserts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2011
    Posts
    10
    Quote Originally Posted by r937 View Post
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2011
    Posts
    10
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tanra View Post
    Or am I missing anything?:
    yup

    INSERT IGNORE

    or

    INSERT ON DUPLICATE KEY UPDATE



    it is never necessary to do a SELECT first to see if it exists already
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    Those are MySQL specific options, and other database systems may not implement them.
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2011
    Posts
    10
    Quote Originally Posted by r937 View Post
    yup

    INSERT IGNORE

    or

    INSERT ON DUPLICATE KEY UPDATE



    it is never necessary to do a SELECT first to see if it exists already
    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

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •