November 13, 2007 at 1:26 pm
This is a question about maintaining the data integrity within the database system I'm using.
Currently I have a User table, that doesn't change much, but several different programs use it.
Essentially what I'd like to do is have tables in one database use a foreign key on a column that is in a separate database.
An example:
Database 1: "CommonDb"
Contains Table: UserInfo
Columns: Login_Name(PK), Full_Name, User_Title
Database 2: "Sales_Store"
Contains Table "Sales"
Columns: Sale_Id(PK), TimeStamp, AddedByUser, Amount
Database 3: "Account"
Contains Table "Accounts"
Columns: Account_Id(PK), TimeStamp, Account_Manager, Current_Balance
And the Idea would be that the "AddedByUser" field and the "Account_Manager" field would use the user LoginName from the CommonDb.
Perhaps there is already a good way of doing this, I'm not to sure, but any thoughts and suggestions would be welcome.
Kris
November 13, 2007 at 3:43 pm
MS SQL does not really support this.
This is not a typical thing to do. I remember seeing a product that someone sells to do this. I cannot remember what it is called.
You can use a combination of check constraints and triggers (or just triggers) to do this. Check constraints are generally against just the single table, but they can execute functions and functions can look at other tables and even be cross-database.
I would recommend doing this sparingly and be careful to see what it is doing to your database performance. I have implemented it a few times (and am actually implementing it on an application being deployed now), but it takes some careful planning or it can lead to problems.
I tend to put every bit of data integrity I possibly can into indexes, constraints, and triggers (triggers only when absolutely necessary) rather than doing the same thing in application layers or in stored procedures. This can be argued against for a lot of reasons - sone of them pretty good reasons at times, but I have found a couple of things true.
First, if you are doing something in an insert or update stored procedure that relates to data integrity, you can probably do the same thing with constraints or triggers and get the same performance at the same time eliminating the ability to bypass the logic by not using the stored procedures.
The second thing is that there will be a time in which your data will need to be modified with a tool other than the application with all of your business logic designed for your data. Argue all you want, but some new thing will come up in which someone needs to bulk load a bunch of data with SSIS or update a group of records with a query and the dope that does it will not know about the business rules programmed into the application. It would be nice to not hire that guy in the first place, but it is sometimes me, so I have to forgive him.
Please, nobody beat me up on this one. Your argument is good and I agree with many of your points.
November 14, 2007 at 10:21 am
Since this constraint isn't required, but more of something that I'd like to have, I'll probably pass on it. It'll probably needlessly complicate things.
But on the other side of the coin, I know how to implement it now.
Thank you
November 14, 2007 at 7:25 pm
Is there a reason you can't collapse the databases into a single database?
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply