Database Design for Email system

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Database Design for Email system

Author

Topic

owais
Starting Member

1 Post

Posted - 2007-11-13 : 00:44:15

Hello to all out there

I want to design a database for an Email system.

Two options are coming to my mind

1) Whether I will go with a separate table for each user which will contain in each field information like MSGid, recipient, sender, subject, message.

2) or I will maintain the information in a single table for each user.

I am not able to comprehend the pros and cons of each solution above.

Please help.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-13 : 03:50:49

do you really mean a 'table' for every user? maintaining that would be a nightmare, i can't see any reason why you ever want to do that

i'd have thought a good starting point would be something like a user table, a messages table and a recipient table (assuming a message can have more than one recipient?)

i.e.


create table users
(
userID int,
username varchar(50),
EmailAddress varchar(100)
)
go

create table messages
(
messageID int,
senderID int,
subject varchar(100),
body varchar(8000)
)
go

create table recipients
(
messageID int,
recipientID int
)
go

build in your busines logic obviously but that's the sort of structure i'd start with

Em

TimmyC
Starting Member

10 Posts

Posted - 2007-11-13 : 03:53:58

You should use just 1 table. Although I understand your temptation to use a table for each user when you know exactly how many users there will be and it will never change, it is still generally naughty design. In the extremely unlikely event that you want to change how your system runs in the future (of course you will) then you will wish you made it more robust.

Messages:
Msg_ID
From
To
Subject
Message
Importance
etc

You would need some clever code to dynamically create a new table for each user and then later be able to determine which table to read from when a user requests their mails. Also, lets say you forgot to add in a boolean value to each mail "Read". With 1 table you just add it is a column and set the default to 0 (bit). If you had any number of tables you would have to change all of those (making damn sure you don't make even the slightest typo) AND change your code for creating new tables for each user.

How advanced an email system are you planning to make?

TimmyC
Starting Member

10 Posts

Posted - 2007-11-13 : 03:56:24

Sorry, that big paragraph under the table design was in reference to creating a new table for each user - sorry I forget to say what i'm talking about often. People in the office think I'm crazy, really I'm just eccentric...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources