Based Database design for Highspeed Query

Posted on 2011-05-02
Last Modified: 2012-05-11

i am looking for some design help on how to design my tables in a db to achive the folowing.

Lets say my App has users and each user can have on or 1 mio friends. How do i establish and manage these relationships effectively ? My first Idea is to create a Table and lets say call it friendship, then the table would have 2 columns one would be the owner ( who has the friends) and the second one would be friend (the user who he is friend with) now for every friend i create a row. To find all friends of that user i would just query where owner = userid ..

that will work fine but not sure if you have lets say 100000 users and each has between 50 and 250 or more friends that this is the best way to go. So any idea would be great
Question by:AlexPonnath
    LVL 92

    Accepted Solution

    Even so, the approach you started with, which is nice and normalized, is still the best way to go.
    LVL 9

    Assisted Solution

    As matthewspatrick mentioned your logic is the right way to build that but beware if you use MYISAM after 2M lines you may live troubles with your mysql server. So if you plan a huge community INNODB is a better solution. but your SELECT's might be a little bit slower. And also you should think using stored procedures.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how the fundamental information of how to create a table.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now