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]

:p 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?

[Blunty Killer]

Yup you got it. Messages in one table pointing to the person who left it. Storing them as blobs is just fine.

Ahm the issue of images in the database or the file system is a debate that will never end. I personally use the database for images (<200K) to prevent accidental deletion on the file system by some other process.

You would not store a 2MB image on the database as it could cause performance degredation. Large files are best handled by the file system of the server on which the database resides.

[Arch_Angel]

I personally use the database for images (<200K) to prevent accidental deletion on the file system by some other process.Curious, what other process would accidentally delete image files on the server? And wouldn't your web files (asp, php, html, etc.) be in danger of being deleted as well? Unless the processes only target image files. :eusa_thin

Not to mention your database files being deleted by said processes, unless you have your database server on another server separate from your web server. :eusa_thin

If had experience having your images accidentally deleted by a process, I wouldn't mind reading about it.

Personally, I would store the images in a specified folder on the server and point to it from the database.

[Blunty Killer]

Wow, never expected such a narrow interpretation of 'process.' But regularily companies only make backups of dbs and essential os files to get the db up and running in terms of failure. One counting on such an event would have to accept that only essential files will be restored of which your images are not.

As i noted to preempt any debate, anyone can use any method, not really trying to start a debate on the merits of either, the net is already populated with them.

[Arch_Angel]

Wow, never expected such a narrow interpretation of 'process.' But regularily companies only make backups of dbs and essential os files to get the db up and running in terms of failure. One counting on such an event would have to accept that only essential files will be restored of which your images are not.

As i noted to preempt any debate, anyone can use any method, not really trying to start a debate on the merits of either, the net is already populated with them.Not wanting to debate anything at all. Trying to see and understand the reasoning behind why you do, what you do.

And it's the first time I am reading that there is a debate on the net about storing images in a database vs storing them on the server. I have just found it easier to work with the images stored on the server and link to them from the database, than manipulating them with code, when retrieving and storing them in the database.

I would hope Wallizzle and others would be able to understand the pro's and con's with both coming from the convo.

Just 2 points to your response comes to mind.
1. Would the risk of those images being deleted, be the same for the rest of your site's files? So it's not just the images you will need to be worried about as well, yeah? Then keeping a backup of your sites file, either on your pc or else where would be beneficial. (I usually keep a backup of my site on my pc)

2. One would check with a host before they sign up, if they do regular backups of both the database and your entire site.

With those 2 points, this would help lower the risk of your files being accidentally deleted, and if they are, you are able to restore it with your backup quickly.

What's your thoughts on it?

[Blunty Killer]

Not wanting to debate anything at all. Trying to see and understand the reasoning behind why you do, what you do.

And it's the first time I am reading that there is a debate on the net about storing images in a database vs storing them on the server. I have just found it easier to work with the images stored on the server and link to them from the database, than manipulating them with code, when retrieving and storing them in the database.

I would hope Wallizzle and others would be able to understand the pro's and con's with both coming from the convo...

Ok, apart from personal preference there was an article posted on Oracle sometime ago that addressed this issue. After reading I had agreed with the points presented by the author and applied them to my own projects.

Here is an excerpt from the Manageability section of the same document:


Images stored in the database can be directly linked with metadata. In the one transaction an image can be manipulated, a thumbnail of that image created and all associated metadata modified. Related information is kept in sync. If an image is stored in a file system, then it is possible for external processes to delete or modify that image, causing the image itself to either become orphaned or lose synchronicity with its corresponding relational data. Another common issue is web quality images losing their associated thumbnails, meaning web page displays become broken.

I have highlighted a particular section which shares my sentiments about storing images in the database.

The article can be found here - The Move To Store Images In The Database (http://www.oracle.com/technology/products/intermedia/htdocs/why_images_in_database.html).

Again this is my personal decision to apply a technique I learned while reading another document. It can work for some, and not for others. I encourage everybody to read as much and learn as much about these issues and make a decision about the environment which you work. My environment does not give me control over backup processes of the servers which my applications are deployed. When I install an application, the company takes over archiving. If they choose to backup only databases and nothing else, I would not not want my application relying on any external files that it itself cannot reinitialize such as reports, ini files, etc. Things that are a part of the installation package.

[Arch_Angel]

Thank you. That's all I wanted to know. :)

[owen]

@Blunty Killer I've read that article and they use the phrase "not possible" far too often. For future reference find a better article or write one yourself.

From what I've read Wallizzle doesn't seem to understand the concept of a table as a list of rows and columns. You need to read up some more on that. "A table has a specified number of columns but can have any number of rows. (http://www.google.com/search?q=define&#37;3Atable&start=0&start=0&ie=utf-8&oe=utf-8&client=flock&rls=FlockInc.:en-US:official)"

[Blunty Killer]

@Blunty Killer I've read that article and they use the phrase "not possible" far too often. For future reference find a better article or write one yourself."

LOL! Are you serious? LOL!

Ok, In the article the phrase "not possible" is used twice (didn't know this was far too often).

Here is the first under security


If all images are stored in a directory then fine grained control is not possible. This reference only bolsters the use of databases because it clearly states that using a directory removes fine grained control.

Second time it is used in the article is here:


That is, it is not possible to restrict access of the images to individual users.

This also means that it is impossible to restrict other users from accessing those files whereas in the database you do have control.

I do not know the purpose of your post, but I did read the article and found the article very informative, insightful, and again presenting techniques that I employ.

Strange, you are the first to say the article is not good.

[owen]

You can tell its not a rounded article because of how it goes about developing it points. Its hard for me to explain but its largely biased towards Piction and Oracle. It really doesn't explain anything substantial. Basically "put pictures in database is better". Nothing about indexing or clusters or partitioning or table sizes. as I said before a better article would be good.

[Wallizzle]

@Blunty Killer I've read that article and they use the phrase "not possible" far too often. For future reference find a better article or write one yourself.

From what I've read Wallizzle doesn't seem to understand the concept of a table as a list of rows and columns. You need to read up some more on that. "A table has a specified number of columns but can have any number of rows. (http://www.google.com/search?q=define%3Atable&start=0&start=0&ie=utf-8&oe=utf-8&client=flock&rls=FlockInc.:en-US:official)"

Hey man I do understand at least that... this thread was not meant for bashing what little I know about dbms... I want views, ideas, n opinions... now if you may just focus on whats at hand plz... thanks man

[Blunty Killer]

Yes, let's continue the mission of helping you. So Wallizzle I have imagined you have grasped the use of your intermediary table, now to the messaging portion of it, have you decided how you wish to tackle that? By the way, what backend are you planning on using, MySQL?

[Wallizzle]

Mysql db... I was thinkn i was plannin on storing those in a table (lets call it Mail_table). which i would have auto_int [id], user_id, sender_id, and then the message recieved(data type blob i guess), then make another column that says reviewed... so I can tell if it has already been read, which probably would hold value true/false, btw does mysql allow boolean values?

[Blunty Killer]

You should try and separate messages in their own table for broadcasting. you can just enter one message and then attach that way you can enter in your like delivered messages table, many entrants pointing to one message. So try and abstract that message from your user.

Ideally a message should not know about a user. A message is just words. The delivery information should be handled by a 'broker' of sorts. Your broker will then based on information from the sender give the recipient a message id and say, 'here get this message from the server and read it.' When you have that level of abstraction, you can say to a hundred users, 'here is a message id, go and read it' without duplicating the message itself for all users.

Hope I am making myself clear.

[Wallizzle]

Dang the thought itself is kinda abstract rude boy... so wat u sayin dont insert a user_id in the message table, however i should let the point the user to the message Id.... now i would have to create another table to link the user_id to the message... is that what your saying?

[Blunty Killer]

Yes it will save you alot of headache. Just have one table with delivery information. This table should have a sender, recipient, message_id, date_delivered, etc. All information necessary to know about the delivery of a message. You then attach the message id to each delivery entry. This way you can broadcast your messages. So you could make 4 entries from user A with recipients B,C,D,E and have all entries point to message id 15. That way you do not have to put the message beside each recipient. You just use one message for all intended recipients.

[Wallizzle]

well i neva thought about it that way.... man how long have you been doin this kinda thing... like forever huh? ur the greatest.... nuff blessings

I'll be sure to link u wit anything other issues i run into. wat other languages u know?