User's Manual

Chapter 8. SQL Remote Design for Adaptive Server Enterprise
Publication design for Adaptive Server Enterprise
Once you understand how to create simple publications, you must think
about proper design of publications. This section describes the issues
involved in designing publications, and how to take steps towards sound
design.
Design issues overview
Each subscription must
be a complete relational
database
A remote database shares with the consolidated database the information in
their subscriptions. The subscription is both a subset of the relational
database held at the consolidated site, and also a complete relational
database at the remote site. The information in the subscription is therefore
subject to the same rules as any other relational database:
♦ Foreign key relationships must be valid For every entry in a foreign
key, a corresponding primary key entry must exist in the database.
The database extraction utility ensures that the CREATE TABLE
statements for remote databases do not have foreign keys defined to
tables that do not exist remotely.
♦ Primary key uniqueness must be maintained There is no way of
checking what new rows have been entered at other sites, but not yet
replicated. The design must prevent users at different sites adding rows
with identical primary key values, as this would lead to conflicts when the
rows are replicated to the consolidated database.
Transaction integrity
must be maintained in
the absence of locking
The data in the dispersed database (which consists of the consolidated
database and all remote databases) must maintain its integrity in the face of
updates at all sites, even though there is no system-wide locking mechanism
for any particular row.
♦ Locking conflicts must be prevented or resolved In a SQL Remote
installation, there is no method for locking rows across all databases to
prevent different users from altering the rows at the same time. Such
conflicts must be prevented by designing them out of the system or must
be resolved in an appropriate manner at the consolidated database.
These key features of relational databases must be incorporated into the
design of your publications and subscriptions. This section describes
principles and techniques for sound design.
Conditions for valid articles
All columns in the primary key must be included in the article.
147