Page 1 of 1
Proper Database Structure that may support IM
Posted: Thu May 29, 2008 8:21 am
by joefazee
I’m planning a web application, but later the scripting may change, but I don’t want the database structure to change, so for the start I want to get it right.
Note: The registration form will contain: username,password,fullname,email&country
The application will later have an IM (instant messenger) just like Yahoo/MSN, so what is the proper way to store the user’s information now.
Then later I will find I way of connecting to it.
But if you have any knowledge of how is properly done, I will appreciate it.
I`m using MySql/PHP, But the IM will later be written in Python.
Please tell me what I need then I start making necessary arrangement towards it.
Thank you.
Abah
Re: Proper Database Structure that may support IM
Posted: Thu May 29, 2008 2:03 pm
by califdon
Maybe someone else has specific experience with IM applications, but from my limited knowledge, the only fields I would suggest adding to what you listed would be an auto_increment record ID (absolutely essential!) and a date joined field. You might want to consider adding a membership class or group, but that all depends on how you are going to develop your system.
Re: Proper Database Structure that may support IM
Posted: Thu May 29, 2008 2:19 pm
by onion2k
You (joefazee) seem to be implying that you IM application would connect to the database. That would be wrong. You'll need to write a server application that the IM clients connect to. The server app is what will connect to the database.
Re: Proper Database Structure that may support IM
Posted: Thu May 29, 2008 4:15 pm
by joefazee
To (onion2k) I`m planning to write the Chat server with Python as time goes on.. The server will connect to the database, then i will write client to connect to the sever, the client is to send request and the server is to handle the request then send back response based on the request from the client.
All i want now is a proper database structure that does not did to change during the development of the Chat server.
Code: Select all
MySql
CREATE TABLE (
userid INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(45) NOT NULL,
pwd VARCHAR(65) NOT NULL,
email VARCHAR(45) NOT NULL,
fullname VARCHAR(25) NOT NULL,
dateReg DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
dateLastLogin DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
ip VARCHAR(18) NOT NULL DEFAULT '00.00.00.00'
)ENGINE=InnoDB;
Note: Please i have not execute the SQL i just typed it here as a sample.
What did i need to add or remove or append?
Re: Proper Database Structure that may support IM
Posted: Thu May 29, 2008 5:12 pm
by onion2k
Beyond what you have there I would add:
Status (online, away, in a call, etc)
Screenname (Different to username and fullname)
Any social metadata (date of birth, home town, favourite colour etc ... that could all be shifted to a linked table though I suppose)
Block status (Does the user accept random messages and friend requests from unknown people?)
Last client version number (Possibly useful, though it could be gathered at runtime when someone connects)
You'll also need a many to many table for friend lists if you want to store them on the server, and possibly a table to store a history of all the messages if you're using this thing for work stuff.