As Brian says, the use of separate databases in the book and sample application is to demonstrate the capabilities of the framework. As for implementing in this fashion with the need to enforce referential integrity, well now you're talking about a DB design issue and there are a number of solutions. I believe, but may be mistaken, that SQL Server does allow you to have foreign key relationships with tables contained in another db - but again, I could be mistaken.
Anyway, a good way to think about this from a framework standpoint, and to possibly help your thinking about referential integrity a bit, is to look at an application using "integrated" security - i.e. the user information comes from the OS, possibly Active Directory. We do a lot of these applications and there is no way to enforce referential integrity in the db when the user's identity comes from outside that data store. (We have another case where the user information comes from Active Directory, their employee profile data is contained in an Oracle database but the applications are all written to use SQL Server!).
The point is that under these circumstances you have no choice but to evaluate this and how you are to maintain referential integrity.
Well, go back to the initial chapters of the book where Rocky discusses a lot of the decisions that were made and optional paths he could have taken. One of the first questions any design team faces when developing a data-driven application is where to put the logic. With CSLA, while making use of stored procedures in the database (for reasons stated in the book), the decision was made to encapsulate the logic - or business rules - into our objects rather than in the sprocs. When you consider that referential integrity is really just another business rule (although it has other implications as well), it then becomes logical that enforcement should also fall to your business objects.
In our case, we have no choice but to store the user's login name (Identity.Name) in our database tables because that's all we have to work with. But, from that, we are able to retrieve the information from all three data sources - which is our goal. Is there anything that says that the user listed in the CreatedBy field still exists in Active Directory when that record is retrieved? No. But do they really need to be? The answer may be "yes" depending on your use case, but if it is just a tag for traceability, the user name tends to be enough. And if not, you can create a lookup table in your database that relates the user name to their actual name and whatever else you feel must be persisted in your application. And, in our case, if for some reason their profile is no longer available in the Oracle database, the link to access this information is disabled.
Should you be in the position to work within a single data store, then the decision to split databases should be based on security and possibly loading concerns. In the case of the latter, I think today's database servers are able to put aside many of the issues that us old-timers had to deal with so it really boils down to what security you'll have on the db. You may have many DBA's running around with different security levels and feel that isolating the more sensative information calls for a segregated design. That's fine and you can use the same approach as above and everything will work well. On the other hand, today's databases also implement much more thorough security models that allow you to fine tune access right down to the object level so there isn't that much gained by splitting the databases.
But, to digress, the point of doing so in the book and example was, again, to demonstrate that you can develop an application that combines data from multiple, disparate sources. Whether you actually do so or not depends on you, the requirements of your applications and the environments you find yourself working in.
Hope that helps.
Copyright (c) Marimer LLC