Relationships and constraints across databases

(I wasn't sure which forum to pos this in. Let me know if you think there is a better one.)

I have an application that breaks down into two sets of data, Set A and Set B, for our purposes. Both Sets currently reside in the same database. Set A is a fairly static set of data, changing maybe once a week, if not less often. Set B is empty at first but it has a lot of dependencies to Set A. Items are chosen from Set A to fill Set B but never the otherway around.

Here is the problem. We continue to copy the "master" database whenever a new customer signs up. The problem is, anytime we have to update data on Set A we have to do it across multiple databases. Set B is never copied as its customer owned.

What I would like to do is create a separate database that contains Set A data only. Whenever we have a new customer, I create a new database that contains Set B only. I have done so, but the relationships and constraints cannot be done unless I use triggers and procedures.

Is there anyway to achieve what I am trying to do without using triggers and procedures?

Thanks in advance.

Hi, actually no. Cross database integrity has to be achieved through triggers.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Another alternative in 2005 would be to use schemas instead of different databases. Then all of your data would be in the same database, which would be better for your data security, easier to program etc. You could break the schemas out into filegroups if you wanted for backup purposes too.

Then you could use DRI and not have to resort to triggers (which can be really problematic to manage and keep the data in sync (though not by any means impossible.) The problem being that you need a trigger on the parent AND child table, to protect on inserts/updates of children, then for deletes/updates of parents.

|||

These circumstances are the sole purpose of triggers (but not always sp_'s).

There is only one alternative but has more tradeoff's than it's worth. Dump everything into one table but you've already discovered this.

Adamus

Relationship constraints

Hi,

I am trying to get the best solution for my problem:

I have 2 tables. Customer and Transaction. A transaction can contain a customer but not always. As I see it I would have 3 options in doing this.

1. Create a CustomerId field in the Transaction table and create relationship on the field but not enforced it and allow NULL or 0 for non customer related transactions.

2. Same setup as 1 but load a "dummy" customer and setup the constraints correctly. But I have a problem that I already have data in my customer table, so I can't take the easy route and only select the 1st customer as my "dummy" customer.

3. Create a CustomerTransaction table with TransactionId and CustomerId and create proper relationships.

What would be the best practice handeling the above scenario?

Regards,

Adriaan

Hi Adrian,

Remember that a foreign key constraint can ban be built upon a nullable column, so if you had migrated your CustomerID column from table Customer to table Transaction, and created it as nullable, then this would meet your needs (your option 1)

Do not go down the path of Option 2 as it's always ugly...

Option 3 will still leave you with a table identifying that a particular transaction did not involve a customer (a null CustomerID in the CustomerTransaction table) - this is no different then option 1 but introduces additional entities.

You've posed a design question, so I think you'll find that there is no "correct" answer and my idea of best practice will not be some elses ;)

Cheers,

Rob

|||Hi Robert,

Thanks for the reply I never realized that foreign key constraints can be built upon nullable columns, I just assumed that it would not be possible.

I always like to have a simple table structure and try not to introduce extra entities where I should not.

Thanks again!

Relations between tables - contraints diagram

Hi all,
I have a big problem. I have many tables with constraints, with foreign keys. I need to create a ordered list of tables, on the top must be the basic table what has no parents, then the second level tables (those depends on the first level) the the names of third level etc.

for example:
Table A[id]
Table B[id, idc]
Table C[id, ida]
Table D[id, ida]
Table E[id, idc]

I tried it by using information_scheme but I was unsuccesfull.

The result should be:
A
C
D
B
E

Thank you,
Tom.There isn't always a relational solution to this problem, because users sometimes create non-linear (aka graphical) relations. I know... Bad user! Naughty user! No donut!

I worked up an iterative solution, but I don't have it handy. If no one else posts a solution (you still have a copy of that one Rudy?), I'll dig it up and post it, but it might be a day or three.

-PatP|||thank Pat,
I apreciate your help, I am also working now on a iterative solution, but it does not work. So if you will have a time I will be very glad to see your solution and help, three days or five, does not matter.
Thanks again.

Tom.