Database Reference
In-Depth Information
ate a physical model based on your logical model. If this is the case, all you
have to do is work through any name changes required to match the stan-
dards. If your modeling software doesn't support physical models, or if you
have been building your physical model right inside SQL Server, then you
have more work to do. It isn't complicated; you just need to start from
scratch when it comes to creating your tables. All we can say is that, al-
though expensive, good modeling software is worth its weight in gold.
One last thing we want to talk about is the business rules that need to be en-
forced in your model. Many business rules will be set up and managed in the
application or in a middle or business tier, but some rules can and should be
implemented in SQL Server. We do this for two reasons: First, it makes
sense to enforce some things, such as constraints on data, in SQL Server; and
second, as database developers, architects, and administrators, we don't trust
applications. Is this just paranoia talking? Maybe, but it is still good practice
to implement as many business rules as possible in SQL Server.
To be fair, it isn't only because we don't trust applications; other factors
push us in this direction. For example, let's say you have a perfect business
tier and all the data comes through without failure each and every time;
would it then be a good idea to remove all your PKs, FKs, and constraints
because the business tier is handling the relationships and data integrity?
Some people argue that it is, but we disagree. What happens if for some
reason you need to run a bulk import and you do it without the aid of the
business tier? You stand to create a lot of integrity problems because of a
lack of enforcement in SQL Server. Also, certain features of SQL Server,
such as transactional replication, require primary keys. We are firm believ-
ers in using any and all means in your power to maintain data integrity in-
side SQL Server databases. In this section, we look at how to implement
business rules using various features of SQL Server and we give examples
from the Mountain View Music database for each feature.
Using Constraints to Implement Business Rules
Constraints provide a mechanism inside SQL Server to control the data
that goes into tables. We look at three types of constraints: default, unique,
and check constraints.
Default constraints
provide a default value for a
Search WWH ::
Custom Search