frustrating question

[droidus]

i have been pondering how you would set this scenario up for a while.  such as on this site, with private messages, and the inbox; how would you create a database that will hold the messages, and their sender, date, other data, etc.?  lets say i send message 1: "hello there!", then 2: "how are you doing?".  when this goes into the database, so i can later echo this on the member's index page, how would i organize, and echo these messages?  no detailed code needed, just need "pseducode"/something to think about.  what would be the best way?  separate messages by commas?

[jcbones]

Here is a table structure that I used in the past.

CREATE TABLE IF NOT EXISTS `instant_message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_of_event` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `recipent` int(11) NOT NULL,
  `sender` int(11) NOT NULL,
  `message` text NOT NULL,
  `replied` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `resolved` int(1) NOT NULL,
  `sender_delete` int(1) NOT NULL DEFAULT '0',
  `recipent_delete` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

[droidus]

what happens when you have more than one message in there?  how do you seperate them?

[trq]

Each message creates a new record in the database.

[silkfire]

It is also key to have one field called "unread" with type ENUM('','yes') so as to know if the user has read it.

[droidus]

wow, so you could have, in your database records:

 

user    | message | unread?

user 1 | Hi there! | yes

user 2 | how are you | no

 

that seems a little tedious, and unorganized?  but that is how you do it?  just insert a new record every time, for each user, for each message?

 

it seems like if you did this with any popular forum, you would have millions of these!! =D

[cunoodle2]

Yes, there would/could be millions of them.  There are millions of records in phpFreaks DB for thread responses and other items.  How many records do you think there are in say ebay and/or facebook tables?  Did you have something else in mind?  Maybe give us an example of what you were picturing in your head.

[silkfire]

Database tables can have millions of rows it won't necesarilly make them slower as long as you use indexes.

[Psycho]

user    | message | unread?

user 1 | Hi there! | yes

user 2 | how are you | no

 

No it would be more like this

user_id_from | user_id_to | message     | created             | read
10          |  6         | Hi there!   | 2011-08-24 10:25:16 | 1
10          |  6         | how are you | 2011-08-24 10:25:46 | 1
6           |  10        | I am fine   | 2011-08-24 10:26:33 | 0

 

You would use a foreign key for the user id that points back to a record in a user table.

[droidus]

no familiar with "foreign key".  does that just mean you are pointing to another record in the database?

 

this is interesting!  so i would index it for sure (any good tutorials? still have to learn how to do this... )  also, would i organize the database, when inserting records?  maybe by last name?  or is that what indexing is mainly for?

[Psycho]

no familiar with "foreign key".  does that just mean you are pointing to another record in the database?

 

Yes, your user table would have a column for the primary key - which would be set as an auto-increment int field. That way the value will always be unique. So the user table would have some fields such as

user_id | username | date_created | password | etc. . .

 

So, the '6' and '10' from my first post would point to the records in the user table with the user_id of '6' and '10'. Also, on second thought, you might also need a unique ID for the messages table in case you need to allow for user to edit/delete the messages. You would need the unique ID to determine the record to update/delete when you run the query.

 

. . . this is interesting!  so i would index it for sure (any good tutorials? still have to learn how to do this... )  also, would i organize the database, when inserting records?  maybe by last name?  or is that what indexing is mainly for?

 

There are tons of tutorials on the net, just do some searching. But, take a look at a few different ones. Although there is lots of good information out there, there is also lost of bad information. I like the tutorials on Tizag which are good for explaining the "how to", but I'm not sure they have anything on the concepts for creating database structure.

 

As for "organizing" the database - you don't - just add the records as they come in. You can SELECT the records you want in any order you want when you need them.

[jcbones]

May I suggest a series of video's that you need to watch

  Make sure you watch all nine of them.  You should then be able to normalize a relational database.  This will help you in your current and future projects.