Database design - multiple physical databases

Database design - multiple physical databases

Post by Ben McEw » Tue, 10 Mar 1998 04:00:00


Quote:> In a data modelling class recently, it was pointed out to me that the RDBMS
> does not support referential integrity between separate physical databases.
> E.g. if customers and orders resided in separate physical databases on the
> same server RI could not protect a customer from being deleted who has orders
> in the other database.  (This is a hypothetical example, but we have a
> similar situation.)

You cannot use declarative referential integrity. That is, I cannot
declare a primary key in table db1..customer, and a reference constraint
or foreign key constraint on table db2..orders. I can, however, roll my
own RI constraints using triggers.

This scenario is, however, rarely done. To answer your next question...

Quote:> It sounds like, ideally, an enterprise database should then be in one
> physical database.

Data that should have DRI constraints (data dependencies) between them
belong in the same database.

Quote:> Can anyone confirm this last notion for me?  Also, given that we do currently
> have separate physical databases, does anyone have any ideas about how to
> back out of that situation?  I thought about moving the physical data to the
> primary database and replacing the individual tables with views that point to
> the actual data in the primary database.

You can write triggers to fix your current situation. You could use views
the way you suggest, but at a performance cost. Finally, if your data is
still consistent after working for a while without DRI constraints in
place, there are probably other things keeping your data that way.

Examine the possibility of using triggers to enforce RI. Also consider
how users modify data. Perhaps DRI is unnecessary. Do you have a front
end that keeps things in agreement? Also keep in mind that DRI
constraints impose a performance penalty, although it is relatively

Ben McEwan, President, Geist, LLC


1. Physical database design

Can anyone recommend a good book or resource on physical database
design.  There are several books on the market that discuss logical
datamodeling.  Often the physical implementation of these models is
quite different due to performance and practicality.


Sent via
Before you buy.

2. DB online Synchronization?

3. Sybase physical database design

4. How can I see Query source text

5. debate on (physical) design database

6. How can I get a Database for DOS 6 ?????????

7. Physical database design

8. Mailing List Software with Sybase Backend

9. Physical Data Modeling and Database Design

10. Physical database designs in Oracle7 for data analysis

11. Physical Data Modeling and Database Design

12. Spanning a database across multiple physical devices

13. Multiple backends on a single physical database