MySQL Forums :: Database Design & Data Modelling :: N-to-N Model: Table Size vs Complexity


Advanced Search

N-to-N Model: Table Size vs Complexity
Posted by: Adam Dougherty ()
Date: September 02, 2008 03:18PM

I've been instructed to add basic social networking functionality into a website I'm working on. I am entirely familiar with the fundamentals of the N-to-N relational model for social networking:

CREATE TABLE `friends` (
`userID` MEDIUMINT NOT NULL ,
`friendID` MEDIUMINT NOT NULL ,
INDEX ( `userID` , `friendID` )
)

And that is of course linked to a users table. Very basic stuff. However I am a bit undecided on how to STORE the information as there are two different ways to do it:

Assume I wanted user 1 and user 2 to be friends. Storing information in the typical way (the way most sites do it), I would just store 1 record for the relationship (of both people) and use a UNION select to get all the friends:

+--------+----------+
| userID | friendID |
+--------+----------+
|     2 |       1 |
+--------+----------+

Select userName FROM users WHERE userID= ANY(
SELECT userID FROM friends
WHERE friendID = 1
UNION ALL
SELECT friendID from friends
WHERE userID=1)

This, of course, eliminates redundancy, allowing for a smaller table. It simplifies the storing of information, but complicates the retrieval of information as it forces me to write a query that reads from the table twice (although I am not 100% versed on the optimization intricacies of UNION). It also complicates several other aspects of my design, including but not limited to "friend categories".


The alternative method:

+--------+----------+
| userID | friendID |
+--------+----------+
|     2 |       1 |
|     1 |       2 |
+--------+----------+

Obviously this will increase table size, but will simplify retrieval queries as I will no longer have to UNION select (reading the table twice) and it allows me to extend the table if I need to (i.e. friend categories).

Weighing the pros/cons of both storage methods, I imagine many people have run type of dilemma before, so I'm just trying to gather some input before I finalize my schema.



Edited 1 time(s). Last edit at 09/02/2008 05:51PM by Adam Dougherty.

Options: ReplyQuote


Subject Written By Posted
N-to-N Model: Table Size vs Complexity Adam Dougherty 09/02/2008 03:18PM
Re: N-to-N Model: Table Size vs Complexity Huu Da Tran 09/03/2008 08:04AM
Re: N-to-N Model: Table Size vs Complexity Adam Dougherty 09/03/2008 01:04PM
Re: N-to-N Model: Table Size vs Complexity Huu Da Tran 09/03/2008 01:29PM
Re: N-to-N Model: Table Size vs Complexity Rick James 05/05/2009 09:37PM
Re: N-to-N Model: Table Size vs Complexity dimitar nenchev 05/18/2009 04:54AM
Re: N-to-N Model: Table Size vs Complexity Rick James 05/18/2009 09:48AM
Re: N-to-N Model: Table Size vs Complexity P. White 05/14/2009 08:33AM
Re: N-to-N Model: Table Size vs Complexity Rick James 05/14/2009 10:27PM
Re: N-to-N Model: Table Size vs Complexity P. White 05/17/2009 11:57AM
Re: N-to-N Model: Table Size vs Complexity Rick James 05/17/2009 02:05PM
Re: N-to-N Model: Table Size vs Complexity dimitar nenchev 05/18/2009 04:50AM
Re: N-to-N Model: Table Size vs Complexity P. White 05/20/2009 08:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.