Php Messaging System

[Wallizzle]

I am interested in creating a php messaging system...
its like a personal phone book environment. Add friends to your list, and send them messaging, was planning my database when i ran into a couple of problems...

1. if a field in database table can contain a max of 255 characters... how can a user store so many friend ids? is it best to use files? and if every member using the phone book where to have a file created for each, what happen if > 1000 members? > 1000 files?

2. how do i store messages that the users send to each other... n how would i keep track of those... its like a very simple version of hi5.... much more simple... but how and where do I store all those messages.

I would realy appreciate any help I can get...
Lemme hear yu views/ideas or if there are articles I can read... cuz its been bugging me out

[Utech22]

read more: http://dev.mysql.com/doc/refman/5.0/en/blob.html

[Blunty Killer]

You should not need to create files, the database should be used for those sort of things. For example, let's say you have a user and each user is identified by user_id. In your users table you could reference your friends table by adding entries to that table that belong to a user.

So for example, users would have a column structure like:
user_id | user_name | nick_name | full_name

Your friends table would look like:
user_id | friend_id

Whenever a user adds a friend, you add an entry into the friends table for that user and the friend they added. You then query the friends table for all friends of a particular user.

[Wallizzle]

RRRooo??? im lost there man... ok so lets say i have a field in the members table for friends, lest say friends_list (data type blob), which would store user ids for the friends a user has? Got that part. So "user A" adds "user b" as a friend, i update also "user b" friends_list with "user a" id simultaneously? Saying wat u saying back in simple terms... am I correct? and u saying blobs would be good for that kinda thing? long_blob? medium_blob? what ya think?

[Blunty Killer]

RRRooo??? im lost there man... ok so lets say i have a field in the members table for friends, lest say friends_list (data type blob), which would store user ids for the friends a user has? Got that part. So "user A" adds "user b" as a friend, i update also "user b" friends_list with "user a" id simultaneously? Saying wat u saying back in simple terms... am I correct? and u saying blobs would be good for that kinda thing? long_blob? medium_blob? what ya think?

That's the idea but you should not even be considering blobs for that. Blobs are for raw data, like pictures, memos, file chunks, etc. What you want to do is to add and entry into the friends table, essentially creating a new row (this is where the power of rdbms comes in, being able to query millions of rows quickly).

So if user 'A' adds user 'B', 'C', 'D', 'E' to their list, your friends table should look like this:
friends
user_id | friend_id
A--------B
A--------C
A--------D
A--------E

Now when you want all friends of A, you build a query like this,

SELECT friend_id, nick_name FROM friends
LEFT JOIN users ON friend_id=user_id 
WHERE user_id='A'

Dont think about blobs just yet, and the logic to maintain a blob list is not worth the hassle.

[Wallizzle]

Dang thats wierd... so wat ur saying is that when I add a friend... a new column is created in the friends table that would hold my id? damn... that means... I would have a table for each member? is that the idea?

[Blunty Killer]

New column? No. A new row. You do not need multiple tables for multiple friends, you need only one table in which each friend will occupy a row.

[Wallizzle]

ok I think I understand now... the table name friends will have 2 columns...
lets call them friendA - FriendB

now when I add a friend, my id goes in friendA and the person I add, their id Goes to friendB? wow I think I understand now... makes sense.... n I can even make it a composite key with those 2 columns, so no duplicates allowed rite?

[Blunty Killer]

Now you got it. The composite key would not apply there because it would be an intermediary table. A composite key usually applies where you have no unique key but two keys together make the tuple unique. For example, zip code and address. There are many '10 First Street' addresses in America, but only one '10 First Street' at zip code '11211.' So composite keys are only needed when needing a unique reference where one is not already available on one column (usually a serial or autoincrement column depending on your database).

[Wallizzle]

so I may also have a table called messages rite? user id in one column and have the messages stored as blob in another? what do you think? and do u recommend storing user images like this or do u recommend storing images directly to the server, and save the img location to the database, to reduce size of database.... and easier database management and maintennance?

the reason I actualy thought composite key is the fact that I wouldnt want a person being able to add a friend twice? is that a wise idear?