Jump to content

need an advice!


kharbat

Recommended Posts

Its an email system..

users have inbox limited to 10 MG, each user can send and recieve messages..
attachments are also allowed.. each user can send and receive more than one attachment.

each user has a profile with picture, if that's possible..
each user has favorite contacts..

user email.. consists of inbox messages, sent items, drafts, and trash bin..

is there any suggestion for tables structure?? i don't need sql statements.. i just need a description
like

table one:
1- bla bla (primary key)
2- blaa blaaa

and so on

thanks for your time
Link to comment
Share on other sites

Before you start designing table structures, I've found it's good to decide, in detail, all of the data you want to store.
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
users have inbox limited to 10 MG, each user can send and recieve messages..
attachments are also allowed.. each user can send and receive more than one attachment.

each user has a profile with picture, if that's possible..
each user has favorite contacts..

user email.. consists of inbox messages, sent items, drafts, and trash bin..
[/quote]
There are many ways to store e-mail data. And your description is pretty vague. Take the time to plan out your data that you want stored and it will make it easier to help you.

dptr1988

BTW This forum is kind of slow. So be patient.
Link to comment
Share on other sites

well, that's the structure i've been using... when a user registers, 2 tables are created 'User'_inbox and 'User'_attachments ..

it logical to store two tables for each user??! is there a better way??



[code]
DROP TABLE IF EXISTS `user_accounts`;
CREATE TABLE `user_accounts` (
  `UserID` mediumint(9) NOT NULL auto_increment,
  `Username` varchar(30) NOT NULL default '',
  `Password` varchar(30) NOT NULL default '',
  `First_name` varchar(30) NOT NULL default '',
  `Last_name` varchar(30) NOT NULL default '',
  `Gender` varchar(10) default NULL,
  `Birth_date` date NOT NULL default '0000-00-00',
  `Country` varchar(20) NOT NULL default '',
  `Citry` varchar(20) NOT NULL default '',
  `Alternative_email` varchar(50) NOT NULL default '',
  `Occupation` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`UserID`),
  UNIQUE KEY `Username` (`Username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;


DROP TABLE IF EXISTS `user_inbox`;
CREATE TABLE `user_inbox` (
  `MessageID` mediumint(9) NOT NULL auto_increment,
  `MessageTime` time NOT NULL default '00:00:00',
  `MessageDate` date NOT NULL default '0000-00-00',
  `MessageState` char(1) default NULL,
  `MessageType` varchar(10) default NULL,
  `Sender` varchar(30) NOT NULL default '',
  `Subject` varchar(150) default '(none)',
  `Body` longtext,
  `Attachment` char(1) default NULL,
  PRIMARY KEY  (`MessageID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;


DROP TABLE IF EXISTS `attachments`;
CREATE TABLE `attachments` (
  `AttachmentID` mediumint(9) NOT NULL auto_increment,
  `Owner` varchar(30) NOT NULL default '',
  `File` mediumblob NOT NULL,
  PRIMARY KEY  (`AttachmentID`),
  KEY `Owner` (`Owner`),
  CONSTRAINT `attachments_ibfk_1` FOREIGN KEY (`Owner`) REFERENCES `user_accounts` (`Username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

[/code]
Link to comment
Share on other sites

I would suggest that you create 3 tables. One for the user_accounts, like you have, one for the e-mails, and one for the attachements. Then add a UserID column in the e-mail and attachment tables so you can connect the users with there emails. That way you only need 3 tables for all of your users.

I'm new to MySQL myself so don't believe what is say! [img src=\"style_emoticons/[#EMO_DIR#]/smile.gif\" style=\"vertical-align:middle\" emoid=\":smile:\" border=\"0\" alt=\"smile.gif\" /] But that is the way I would do it. For me, posting the tables your are using helped me understand what you are trying to do.

Thanks
dptr1988
Link to comment
Share on other sites

Multiple, related tables is definitely the way to go... there are many things about each particular table that I would tweak, but it's more a matter of personal preference. Sounds like you have all of the basic pieces, just make sure that you can easily add more complexity without having to re-build the whole thing from scratch. Also, enforcing that 10MB limit is going to be slightly tricky, since the DB can't do this directly.

[i]BTW, don't be surprised if no-one responds within 2-3 hours... we all have full-time jobs! If I managed to check the boards even once a day that's impressive.[/i]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.