Databases Reference
In-Depth Information
When it comes to data duplication, you can either treat the parent tables as overhead and duplicate them across
databases, allowing you to maintain strong referential integrity (RI), or sacrifice RI in the database by sharding the
parent tables as well. If you decide to shard parent tables, you can no longer enforce RI in the database; but you may
still be able to enforce RI in your code by adding RI constraints to your
DataTable
objects. You can do so by creating a
DataRelation
object in the
DataTable
's
ParentRelations
collection. For example, the following code adds RI to the
DOCS and AUTHORS
DataTable
objects:
1. SqlCommand cmd1 = new SqlCommand("SELECT * FROM Authors");
2. SqlCommand cmd2 = new SqlCommand("SELECT * FROM Docs");
3. DataTable authors = ExecuteShardQuery(cmd1);
4. DataTable docs = ExecuteShardQuery(cmd2);
5. DataRelation dr = new DataRelation("RI",
6. authors.Columns["authorId"],
7. docs.Columns["authorId"]);
8. docs.ParentRelations.Add(dr);
The issue with identity values lies in the fact that an automatic identity is created for each record. But because
the tables are split across databases, you aren't guaranteed to have the same values over time. To solve this issue, you
need to create RI rules that depend not on identity values, but on codes. In the case of the table that stores states, you
create a StateCode column (which stores FL for Florida, for example) and use that column as your primary key and in
your RI rules. This ensures that all databases in the shard use the same values to maintain integrity.
Designing a Multitenant System
The shard library built in this chapter is designed to access data using a round-robin mechanism. New data could
be stored in any underlying database and a breadcrumb is used to temporarily remember which database contains
which data. This works well for certain applications but may prove difficult to use for business applications where
customer records need to be isolated. Let's review another HPS: a schema-separation architecture for customer data
in a multitenant system.
Schema-based security and data isolation were described in Chapter 3. You learned that schema-based isolation
provides a good security model to protect customer data from other tenants in the same database and how to properly
configure access control for this model.
To design this system we must consider the following points:
•
Service Account.
In order to leverage connection pooling for each customer you will need
to make sure your connection strings use service accounts. A service account is a login that
users do not know about and is used only by the system. Each customer will have its own
service account.
•
Login Database.
You will also need to create a central database containing the connection
string each customer should use. Alternatively, you could store customer connection strings in
a configuration file; however, keeping this setting in a database makes it easier
for maintenance.
•
Customer ID.
A customer ID needs to be created for each customer; the customer ID then
needs to be stored in memory (usually in the Session state) so that the system can retrieve the
connection string given the Customer ID.
Figure
9-14
shows how the system can process a request for a specific customer when reading or changing
data. This workflow assumes that the customer has already logged in and is authenticated with the application; the
authentication process is responsible for storing the Customer ID in memory (the Customer ID could be stored in the
application cache or the session state for an ASP.NET application).
