Hey. Can someone give me some tips on how to design the schema of a dbase if I wanted to make say a friendster clone (or facebook or Hi5 or something).

I am obviously not making a friendster clone, but I am working on something that could apply a similar idea.

More generally, I am talking about designing a database to store a huge connected, undirected graph (and a php based service that would be able to browse/search through this graph efficiently) where each node is some entity.

I'd appreciate any comments or links that may help. Thanks!

Tips well let me see, Id get all types of fields you think are appropriate as well as tables names etc.

Then use normalisation. If your unsure on what normalisation is http://en.wikipedia.org/wiki/Database_normalization

well normalization is just to reduce data redundancy...thats fine. But it's the part that you summarized in the first sentence that is more confusing to me.

How do you make the tables? if each node has multiple neighbors, does each node get a table? or does each node get a row, and the row has a list of neighbors? and since it is undirected, each neighbor also has a row of their own?

It's hard for me to explain because well I am confused myself...but I hope I am getting my point across.

What database is it?

hey planetsim, i really thank you for wanting to assist me, but I don't think you are understanding my problem correctly.

I realize how to use databases (and what they are used for), what I am having trouble with is the "schema design" of this database. I know that you can store fields in databases and also what tables are etc etc. I have used plenty databases in my life I want to get tips on how to organize these tables...basically the relational "schema" of this database. (and yes, i "know" sql...)

JPNYC, I am currently using MySQL.

Thanks guys.

There are 3 steps to the development of a db schema

Data Analysis

This is where you itemise all of the data that you want to collect and store: the entities and their attributes

Normalisation

This is where you take your list of attributes and organise them into a logically consistent model. In the process you will redefine what is an entity and what is not so as to elliminate redundancy. You will end up with a list of entities and their relationshipe to one another: an Entity-Relation diagram

DB Schema

You then take your E-R diagram and reduce all of the M:N relations to 1:N and 1:1 relationships in a new E-R diagram. You must pay particular attention to the infamous Fan-traps and Crows-feet that may be present, or that you may inadvertantly produce while doing so.

This final E-R diagram is your Database Schema. Each entity is encoded as an individual table

Then comes the Art. You use your Domain Knowledge, your knowledge of the contents and what you are using it for, to modify the schema for efficient storage and retrieval.

Complicated, huh! Well no one said it would be easy. But anything less than this will result in a db that is disfunctional and full of booby traps. You will put data in and never be able to get it back out correctly.

OK, well, I may be out of line here, but I'll ask anyway. As much as I'd love to know how to hand code everything, I do not. I'm slowly (very slowly) learning how to tweak some pages & apps with the help of very understanding coders. I'm running a site that deals heavily on MySQL & PHP. I've used "templates" (& that might not be the best terms for this) such as WordPress, phpBB & Coppermine. Combining all that, I've managed to get my site up and running. I'm currently looking for a "template" like those mentioned above to make something like TheFaceBook or Friendster. Does anyone know of such an animal? I'd like to stay with GPL apps if possible.