[Webinar] Streamline your web hosting managementRegister Today


Based Database design for Highspeed Query

Posted on 2011-05-02
Medium Priority
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 93

Accepted Solution

Patrick Matthews earned 1000 total points
ID: 35509218
Even so, the approach you started with, which is nice and normalized, is still the best way to go.

Assisted Solution

by:Erdinç Güngör Çorbacı
Erdinç Güngör Çorbacı earned 1000 total points
ID: 35509307
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.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

608 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