Setting Relationship between two tables in two different databases

[Aarvee]

Hi !

For purposes of administration and categorisation, I have split the database into multiple portions ( aka different databases ). For example all my masters are in Masters Database whereas all my Transactions are in Transactions Database. I would like to set up a relationship between say a table in transactions database and another table in Masters Database.

Can someone help me how to do this in SQL Server 2000 ?

Thank you in advance for your support and guidance.

Varadarajan R

 

[TechGnome]

I don't think you can.... relational indexes are only good between tables in the same database. What did you hope to gain from splitting the DB into two like that?

-tg

 

[Aarvee]

I had done the splitting up into databases both for restricting access to them for different users. Also some of the database tables are tables used by only my application and as such does not undergo any modification. Since that database is under my(developer's control) I can keep checking for their integrity and prevent other users from disturbing them which will hamper my application.

In any case, if it is not possible, then why ask for the impossible. I will do a work around as follows.

Whenever any transaction module loads, I will get all the tables required into a dataset and keep using this dataset until the transaction is ready for updating into the backend table. Once the purpose is served, the dataset can be dropped. I hope this will work.

Thanks for the help. Any further advice on my workaround is also welcome.

Varadarajan R

 

[sevenhalo]

You can still join across tables in different databases (on the same server, assuming they're not linked) so it is very possible. You just won't be able to rely on relationships to maintain cross-database integrity. I cross database reference my users to all of the various databases (it allows me to keep each user to one ID for all applications), but it takes a larger effort on the apps part to validate input. I have most of my systems decoupled enough that I can reference the CRUD (Create Read Update Delete) components and validate all the data in the data access tier before it is written to our DB.

When you design like this, you're really taking alot of SQLs design concepts into your own hands. My data access tier is literally just maintaining cross database integrity. I can't imagine trying to build something like this without it though.

The downside is, it adds a considerable amount to your development time. Not to mention, the concept can take a 3NF db back to a 1NF by definition (dba's love it when you propose things like this).

The upside is... Well, more personal preference than anything. If you maintain the db and also write the data access tier; you'd have a considerable amount of control. Much more than simple relationships can offer (don't drop all your relationships for this, this is strictly about cross-db referencing). You could do alot with triggers, yes; but it won't notify the referencing components about your custom validation (this is all changing with ADO.Net 2.0, SQL05 and vs.net05, but I'm not completely sold on it yet, IMO. Things too good to be true usually are ). The trigger can toss an error code which will eventually throw an exception, but it's not nearly clear anough to handle appropriately.

Bottom line: if you're going to do it this way, build your own data access tier. It'll save ALOT of headaches.

 

[Aarvee]

Hi Sevenhalo,

This is really techie stuff to me. But then everything has to be taken as a challenge. I will need some time to understand and work on this. I will revert back.

Thanks and Best Regards

Varadarajan R

 

[sevenhalo]

It's actually not too bad when you get used to it. It's like Legos. You build the blocks and assemble them into whatever you want. It's the same thing hardened C developers have been doing since day one.

(hopes and prays he doesn't get banned for dropping 'The Big C' in a VB forum)

 

[TechGnome]

Joining isn't such a big deal.... server.database.dbo.tablename is all it takes.... but building a relationship that spans isn't possible.... as far as I know.

if you split the DB solely for security reasons.... then your DB/app security needs to be reevaluated. There should never be a reason for your users to get into the database other than through their front end. Also, you can set security on tables, denying them the ability to do anything to it, but still let your application do what it needs to do.

-tg