need an advice!

[kharbat]

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

[kharbat]

nobody can help ??

hello?

[dptr1988]

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.

[kharbat]

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]

[dptr1988]

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

[fenway]

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]