I'm trying to make a database for a buddy list system that needs to carry the buddy adds between users so they are on each others buddy list only when they both accept. Currently I store buddy lists as one line with a username for the buddy list owner and arrays of usernames for the various states such as pending or added. However I have a problem now where when person A makes a buddy request to person B and person B accepts they have to add themselves to person A's list but if they save the line while person A wants to make a change then I have corrupted the buddy lists. I was thinking of making the database use each line for a different buddy item so two names and a status flag and then a buddy list is all the times you name is in a row. Is there some better way or is the list idea alright?
There's a nearly infinite number of ways to do this. The easiest of them would be to probably just store an array called "BuddyList" and have it be an array of ID's. When a user adds them, it only displays the information of other users that have their ID in their own buddy list.
Doing it this way avoids the need for relational data or tremendously complex processing, it does however increase the number of API calls to confirm large buddy lists, though these could be made during the initialization phase of the program.