The best way to desing this

Question

Post reply

The best way to desing this

Sam Jonhnny

SSC Journeyman

Points: 84
More actions
September 28, 2005 at 4:32 pm

#67050
Can some one please tell me what is the best way to do this I really appreciate your help.

Thank
Sam
Design Internet Web backend based database call Email and inside of the database it has stores a list of email addresses for external users who do not want to receive email from CEB. Web and email applications may select, insert, or delete from this table using the following table (Nevermail) and queries:

Nevermail
Email
Varchar(100)
NoSpamDate
DateTime

-- Start Query
SELECT Email, NoSpamDate FROM NeverMail
WHERE Email = @Email

IF NOT EXISTS (
SELECT * FROM NeverMail
WHERE Email = @Email
)
BEGIN
INSERT INTO NeverMail(Email, NoSpamDate)
VALUES (@Email, Getdate())
END

DELETE Nevermail
WHERE Email = @Email

-- End Query

Your task is to redesign Nevermail to support the following features:

1) Support 3 different types of "No Contact" lists:
- No emails
- No phone calls
- No postal mail
A single email address can be added to more than one list. Don't worry about looking up the phone number or mailing address; you only need to keep track of email addresses.

2) Maintain a history of when an email address was added or removed from one of the lists. Your design should allow for an email address to be added to a list, removed, and then added again to the same list.

For output, please provide the following.
1) Create your new table(s) in the Email database. You may provide the SQL script to create table and query or you may provide with Visio diagram or any snapshot of SQL Diagram will do that work.

2) Outline and briefly describe any other database objects or queries that applications would use to insert, select or delete from the database. This could include views, stored procedures, triggers, user-defined functions, etc.

3) Provide a comprehensive solution for world peace, if possible.

Extra point>

1) Think about designing table that is flexible enough to support more than 3 different types as business requirement needs in the future.

2) Think about high performance designing structure so that this application can handle thousands of transactions if necessary.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sam Jonhnny SSC Journeyman Points: 84 More actions · Answer 1

Can someone please answer me. I need help with this.

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

We're not here to do your homework. This site can help you greatly :

W3Schools - SQL

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

Cool site ... just spent a bit of time there ...

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Sam Jonhnny SSC Journeyman Points: 84 More actions · Answer 4

So can some one please at least give me some info of how may table should I create and the type

of relationships???????? I appreciate it.

Thanks

Sam Jonhnny SSC Journeyman Points: 84 More actions · Answer 5

Is this a good start ?

OK thanks for your fishing and drinking advices. Any how this is what I have come up with , So can you assist me in finding the right solution? How come no one is helpful here ?

EMAIL

emailID int 4

EmailAddress Varchar(100)

UserName varchar(50)

DateAdded datetime

AcceptCalls bit 1

AcceptEmail bit 1

AcceptMail bit 1

EmailHistory:

UserID int 4

EmailID int 4

DateDeleted datetime 8

DateAdded datetime 8

User

UserID int 4

EmailID int 4

UserName varchar (50)

Address Varchar(50)

TelNo Char(10)