hello! I have a question about setting up a friends table in a MMORPG, friends system does not like the items system,it has a little attributes,first way,i can set up a table ,which has three attributes,for example :characterid ¡¢friendidx¡¢friendname ,the primary key is playerid and friendid £»second way ,i save one character's friends in one record, such as:characterid¡¢friendname0¡¢friendname1¡¢friendname2... Now i don't know which is good ,please give me some help and Thanks for any help!

i'd go for the first, since it will allow players to have no, one, or a thousand friends.

@krez

but is the query speed affected when we save or load a character's information ?

Friends list sounds like something which could be handled entirely on the client side...

i prefer the second choice since that's my choice in my project...it's more efficient than the first one... and i wonder what advantages the first choice provide? u can simply record your friends count in the beginning

I'd opt for the first way, but thats just me.

-=[ Megahertz ]=-

Obviously the first way.
The second way not only relies on a max number of friends and is less performant/efficient, but it is bad RDBMS design.

Quote:

Friends list sounds like something which could be handled entirely on the client side...

Then the client has to send the friends list to the server on each connect, or the server has to notify the client about all log-ons as they happen (asuming you want online status).

The table "player|friend" is the obvious database expression of the N:M relation you want to build. However, you'll find that most of the queries will likely be "whom do I notify when player X logs on/off" rather than "who is a friend of player Y", so you should focus your performance optimization on indexing on the "friend" field. Then, when player P logs on, you do something like:

for i in (select player from friendslist where friend = P) {  notify i about P}for i in (select friend from friendslist where player = P) {  notify P about i}

Now, assuming a typical player has an average of <50 friends, and you support 10,000 players, and each player description is 20 bytes (4 bytes player Id and 16 bytes name), then you could decide that you just stick it all in a hash table in RAM -- it's only 10 MB. Make sure you checkpoint it to disk when it changes, though :-)

Thanks for all the reply!
but i have a question with the first method, when i save the player's friend information,i must check whether the friendlist table has the friend,if not i must insert this record in the table,sometimes i must delete some record ,insert and delete perhaps happened in a same transaction which also generate many database log;
need we think about this?

When a user actually edits his friends list, you need to modify the table. I don't think there's any way around that. That's something that happens very seldom, though, so a simple implementation is likely sufficient. For example: when player X changes his friends list, delete all entries for player X from the table, then insert each of the entries in the new, updated list. Do it all in a single transaction, and I think you'll do OK, unless your game is all about constantly changing your friends list...