| Author |
Topic |
|
tribune
Posting Yak Master
105 Posts |
 Posted - 2004-02-02 : 14:59:15
|
| I've got an application that uses two databases. One is the Intranet, and the other is the Website database. My problem concerns a trouble ticketing system which resides in the website database. The main table TroubleTicket has the columns OpenedUID and ClosedUID which represent what employee opened and closed a ticket for a customer respectively. In the Intranet, I have a table called Employees with the primary key being EmployeeID. So, EmployeeID would be the referened column in this scenario, and OpenedUID/ClosedUID is the referencing column. How would you handle this scenario? Should I write a stored procedure? How would security be involved? Or should I transfer the trouble ticket system to the intranet and write stored procedures to interact with the Intranet database from the Website database? Or grant the Website database user limited access to the Intranet database?I'd like to hear your reasons for what development path you'd employ. Thanks-tribune |
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2004-02-02 : 15:20:39
|
| Well, you CANT create a reference that spans a database. However, you can implement the same functionality (ex cascading deletes and updates) using triggers. From your post Im not exactly sure what functionality your trying to achieve. Are you trying to cascade updates and deletes? Could you please explain?- Eric |
 |
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-02-02 : 16:39:17
|
I'm trying to achieve relational integrity, not cascading  |
|
|
|
HendersonToo
Starting Member
20 Posts |
Posted - 2004-02-02 : 16:43:30
|
| Let's take a guess that the Employees table exists on both databases in question. I would also assume that it's pushed onto your web database by some mechanism. Otherwise this makes no sense at all.If not, get your intranet replicating out enough of our Employees database columns to get the job done. Also consider not enforcing the ForeignKey constraint on the EmployeeID column.Like the other reply, I can't tell what else you might be trying to accomplish here. |
|
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-02-02 : 16:59:03
|
| I'm trying to ensure that the EmployeeID referenced in the OpenUID and ClosedUID table located in the web database equates to a valid employee in the database table. I only have one employee table with all the employee details in it, and its stored in the Intranet database. Why would you duplicate the employees table? Then I'd have to write a set of stored procedure to cascade updates on the duplicated table...The thing is, I've got several of these issues. I also have a commissions table in the Intranet which needs to reference the Users table in the web database (since each commission given out derives from being associated with a user sale). It kinda seems like more of a pain, but I thought that breaking up a large database into multiple smaller ones was a good idea - for maintenance and recovery at least... |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-02-02 : 17:53:20
|
| By defintion, RI is per Database.You seemed to have applied the physical infrastructure to your business model. You will need a single database to achieve this RI declaratively.. A work around would to be store the employee table in both databases (as already suggested) and use Access Replication.DavidM"SQL-3 is an abomination.." |
|
|
|
HendersonToo
Starting Member
20 Posts |
Posted - 2004-02-03 : 14:35:58
|
| If your application doesn't need to update or insert on the replicated tables, then why not replicate them? Grant them permissions for SELECT only. Think about security here as well. If Joe Hacker comes along and finds a SQL Injection attack is possible on your website, don't give him the keys to view all the data on your intranet too. That's a strike against tying these in as one database.If you have to make updates or inserts on intranet tables from the website, it's not going to be fun.I am suggesting that you replicate a read-only copy of the tables, and only those columns that are absolutely necessary. The table should likely be replicated once daily. SQL Server does support replication, you don't have to write it from scratch. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-03 : 15:45:12
|
quote:
Originally posted by tribune
I'm trying to ensure that the EmployeeID referenced in the OpenUID and ClosedUID table located in the web database equates to a valid employee in the database table. I only have one employee table with all the employee details in it, and its stored in the Intranet database. Why would you duplicate the employees table? Then I'd have to write a set of stored procedure to cascade updates on the duplicated table...The thing is, I've got several of these issues. I also have a commissions table in the Intranet which needs to reference the Users table in the web database (since each commission given out derives from being associated with a user sale). It kinda seems like more of a pain, but I thought that breaking up a large database into multiple smaller ones was a good idea - for maintenance and recovery at least...
Why don't you use an INSTEAD OF TRIGGER?Brett8-) |
|
|
|
|
Across-database Foreign Key Relationships