Design Question

Dear All,
I have the following design issue which I am unsure how to solve.
Here's the key information:
I am writing a tool which will send emails/SMS to registered users. The
registered user data will be stored in one database (we'll call it
DB1). No changes can be made to DB1. All information related to the
tool will be stored in a different database (we'll call it DB2). This
will include the following tables (among others):
- "Sent Email" table to store which user (from DB1) received what email
- "User" table to store individuals who have permission to login to the
tool
- "Email Template" table to store email templates
- "Email Template Log" table to record which user created/edited the
template
My specific problem is how to model these relationships in DB2. Will
DB2 contain a "User Look Up" table which stores the ID (PK) of the user
from DB1? Therefore my "Sent Email" table will have a FK from this look
up table? In addition if the scenario was that I needed to record an
attribute "Don't send me messages" would this be stored on the look up
table as well?
Thanks for any help in advance.
Jose"Jose" <discussions@.avandis.co.uk> wrote in message
news:1135436233.215881.325210@.g14g2000cwa.googlegroups.com...
> Dear All,
> I have the following design issue which I am unsure how to solve.
> Here's the key information:
> I am writing a tool which will send emails/SMS to registered users. The
> registered user data will be stored in one database (we'll call it
> DB1). No changes can be made to DB1. All information related to the
> tool will be stored in a different database (we'll call it DB2). This
> will include the following tables (among others):
> - "Sent Email" table to store which user (from DB1) received what email
> - "User" table to store individuals who have permission to login to the
> tool
> - "Email Template" table to store email templates
> - "Email Template Log" table to record which user created/edited the
> template
> My specific problem is how to model these relationships in DB2. Will
> DB2 contain a "User Look Up" table which stores the ID (PK) of the user
> from DB1? Therefore my "Sent Email" table will have a FK from this look
> up table? In addition if the scenario was that I needed to record an
> attribute "Don't send me messages" would this be stored on the look up
> table as well?
> Thanks for any help in advance.
> Jose
>
I guess you'll need a Users table in DB2. What you won't be able to do is
create a foreign key on it that references DB1. Cross-database constraints
aren't supported. You could create a view in DB2 that references the Users
table in DB1.
Have you considered using Notification Services? All you've described and
more ...
http://www.microsoft.com/sql/techno...on/default.mspx
Both 2000 and 2005 editions of NS are available.
David Portas
SQL Server MVP
--