Cross-database references

Hi,

I'm doing a web application that will get some information from an ERP.

At this moment I have 2 databases:

1) The aspnetdb, where I have the tables for Merbership and Role

2) The ERP database

I need to put my web application tables on one of these two DB's. This tables will reference the users from the membership and some products from the ERP DB.

I will store products requests that will store both UserID (from aspnetdb) and ProductID (from ERP DB). I'm thinking to put these tables on the aspnetdb, so that all web application tables stick together. But, I will loose tha ability to make joins with the ERP database, right?

Do you think this will work? Can someone make some comments about this situation, and give me some tips?

Thank you!

As long as you have a user who is having access to both the databases you can put your tables in any of them.

|||

Querying from multiple databases (besides having security access to both of them) on requires you to fully name the tables/views involved.

Here is the format for a fully qualified table name:

[catalog_name].[schema_name].[table_name]

So, you can join to your heart's content.

What you cannot do is create foreign keys to in one database pointing to tables in another database. So, declarative relational integrity goes out the window.

(You can create database triggers to mimic a foreign key though.)

|||

david wendelken:

What you cannot do is create foreign keys to in one database pointing to tables in another database. So, declarative relational integrity goes out the window.

(You can create database triggers to mimic a foreign key though.)

These tables were on the ERP DB, and I was passing them to the aspnetdb and SQL Server gave me an error about that. I completely forgot about one foreign key constrait, that was poiting to a table on the ERP DB.

Loosing this constraint will not be so critical, right?

|||

cseven:

Loosing this constraint will not be so critical, right?

That is a business decision, not a technical one. It's your business (or your customer's/employer's), not mine! :)

What is the business result of having orphaned records in the table? (I.e., records that point to non-existent recrods in the other database.)

Could other programmers accidentally add in those records to statistics or counts? Is that a bad thing in business terms?

Is it likely that those orphaned records would find themselves used for dropdown lists to "help" choose "correct" records to assign other table's data to?

This is data associated with users. Could it open up a security breach? Does that matter?

What else could go wrong in business terms?

Are any of these potential business problems important? Important enough to spend the time and money to write a database trigger to prevent them?

|||

You are right, altough "my" situation is not so critical (I think so) as your examples. It's a simple internal requests application, and the only cross database reference is the ItemID that I store on the Requests table.

So, for each request i have multiple item lines, referencing to an Item on the ERP products table.

I had the Foreign Key Constraint and only removed it because I moved the tables to another DB, so the better thing to do is to create the triggers.

What I need to do is something like this,right?

http://www.justatheory.com/computers/databases/sqlite/foreign_key_triggers.html

|||

Yes, except that appears to be for the SqlLite database, whatever that is.

You'll need to use SqlServer T-Sql syntax instead.

A few more googles (or the online help) will get you there. Triggers are pretty easy.