Jump to content


Photo

need an advice!


  • Please log in to reply
5 replies to this topic

#1 kharbat

kharbat
  • Members
  • PipPip
  • Member
  • 28 posts

Posted 15 June 2006 - 07:23 PM

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

#2 kharbat

kharbat
  • Members
  • PipPip
  • Member
  • 28 posts

Posted 15 June 2006 - 09:16 PM

nobody can help ??

hello?

#3 dptr1988

dptr1988
  • Members
  • PipPipPip
  • Advanced Member
  • 372 posts

Posted 16 June 2006 - 12:21 AM

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.

Need more help with your project? One of the thousands of programmers, web designers or artists at <a href="http://www.rentacode...d_6764522">Rent A Coder</a> would be happy to help.

Disclaimer: Free advice is usually worth what you paid for it. ( or at least when it's coming from me! )

#4 kharbat

kharbat
  • Members
  • PipPip
  • Member
  • 28 posts

Posted 16 June 2006 - 12:33 AM

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



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;



#5 dptr1988

dptr1988
  • Members
  • PipPipPip
  • Advanced Member
  • 372 posts

Posted 16 June 2006 - 12:41 AM

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
Need more help with your project? One of the thousands of programmers, web designers or artists at <a href="http://www.rentacode...d_6764522">Rent A Coder</a> would be happy to help.

Disclaimer: Free advice is usually worth what you paid for it. ( or at least when it's coming from me! )

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 16 June 2006 - 07:41 AM

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.

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users