Database Design for Social Networking type Site

meigwil

Hi,

I'm building a site, similar to Flickr/MySpace.

I'm unsure as to how to design the databse, and how the table structure should be.

For example, each user will have a contacts list. The only way I can think of to keep track of these is to include a csv type list of names in a 'contacts' column in the 'User' table.

Is there a better way? Should I have a separate Contacts table? If so, how do I ensure that the contacts list is limitless?

Are there any resources on this kind of thing on the web? I've been searching google and this site but have not found quite what I'm looking for.

I'm also unsure as to how many columns a table should hold. Is there a guide or is it purely dependant on the application? How many rows of data is considered too much?

Thanks,

Mei

Roger_Ramjet

The only real limit to the number of rows is the maximum file size of the operating system, or the size of your hard disk. These days even those limits can be overcome through linked tables and distributed databases and clustered servers.

How many columns? How long is a piece of string. You decide on what data you need to store and then create the columns for them. If the table gets too wide then you can split off the less significant columns into another table.

You definitely have a contacts table. Whoever initiates the contact/friend relationship goes in the first column and the other person goes in the second. Some of these sites will also have additional columns to control things like whether the second person agrees, whether the relationship is visible, etc. You'll think of them as you go along.

If you want to store more complex relationships then google for 'storing hierarchical data' and you will find the design and queries already worked out for you (there is an article here somewhere). If you don't know a lot about databases though then you will find it hard going.

Never ever contemplate using a comma separated list in 1 column, even if other people tell you to. The problems and complexities that will lead to are to be avoided at all costs. There are only a very few special cases where that is the correct way to store the data.

Piranha

I think that you should look a bit on database normalization or SQL normalization (the same thing). And then search for database design tutorials. Those two things would allow you to build the database in a way that is as good as possible. I am not saying this to give you a lot of work, I am just saying it since it is always best to have the database correctly designed before you start to fill it with data. The risk is otherwise that you have to change lots of code to make everything work. In fact, you should probably design everything before starting to code. I have done a few smaller things without designing first, and it have always ended up with coding everything 3-4 times.

To answer your questions straight: You should have a contacts table. When you have done the research above you will understand why, if not please post and someone will answer. How many columns a table should hold is about the normalisation as well. The upper limit that the databases can handle differ a little, but I think it is over 1000 columns for each database. And I doubt that you will need that many columns. There is no number of rows that are considered too much. It all depends on how you will use the database, what server you have and so on. RDBMSes are built to handle many rows, in fact it is what it does best.

meigwil

Thanks for your replies. It's given me somewhere to start.

So at it's most basic level, a contacts table would be:

[INDENT]record_id | username | contactname[/INDENT]

I can see how this would be much better than what I proposed earlier.

I don't have much experience (if at all) with databases, so I'm keen to do the donkey work now because I'm sure it will make the coding much easier later.

I'm not too pressed for time at the moment, so I can learn a bit about database design before pressing on.

Thanks again for your help, I'm sure I'll be back with more questions!

Mei

Piranha

No, in its most basic level it would look something like this:

userid - FK to the usertable PK
contactid - FK to the usertable PK
Both of them together as PK

If you don't have much experience with databases I suggest that you start with smaller projects, it will help you to design this huge project correctly. What about a database with all your books? With title, author and publishing house, maybe even a book category. It will give you basic understanding about database design and construction. You could expand it later with your films and your music as well, then you get into some of the more advanced database designing as well. Or do a address registry with name, address, phone, birthday date and other stuff to all your friends.

meigwil

OK. Thanks. I didn't know that two columns could be used as a primary key. I'll have to start looking into foreign and primary keys.

To be honest I'm still undecided if I'll actually code this myself. I've been experimenting with Drupal and I may look into using it for this project. But I've decided to do some research and plan the site thoroughly so I'll know exactly what I want/need.

Thanks,

Mei

scrupul0us

IIRC correctly from my DB Design Class...

ONE user can have MANY contacts (so contacts gets its own table, PK identified by UID, CID)

research relationships and normalization... even drawing helps me

one thing u dont wanna do
user | contact
user 1 | contact a
user 1 | contact b
user 1 | contact c

if the user name changes youll have to update that ENTIRE table

if you go

uid | contact
1 | contact a
1 | contact b
1 | contact c

then if the user name changes lets say, it changes ONCE in the users table and since u are referencing the ID everything else just flows

meigwil

I found a nice article on databases for tags:

http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html

and thought it may be of interest.

Mei

punechap

May be you can find something useful about database design for social networking websites here at http://dbdesign-sn.blogspot.com/

It also has some queries listed on the page.