CREATING THE PHYSICAL MODEL WITH SQL SERVER - A Developer's Guide to Data Modeling for SQL Server - page 198

check constraint would be something like CK_ phone_number. Finally, for
unique constraints we use the prefix “UNQ_.”
Deriving the Physical Model
Until now, we have done a lot of work with entities and our logical model.
Now we walk through the process of building the physical model based on
the logical model. This process is often a matter of deciding what to call
your tables and creating one table per entity. On the other hand, it can be
a lot trickier. In some cases, you need more than one table to represent an
entity, and other times you use a single table to represent multiple entities.
Remember that entities are meant to model real-world objects, such as
customers or employees. When you create tables, they also need to model
real-world objects, but the other concern here is storing the data in a way
that makes sense in terms of a relational database. This is the reason we
don't always see a one-to-one mapping of entities to tables.
To build our physical model, we start by creating one table per entity
and then split or combine tables on a case-by-case basis. Some people like
to do the splitting and combining in a second pass, and others do it as they
go through the model the first time. Ours is a hybrid method. We go
through once, putting things where they make the most sense, and then we
come back and look at what we have done to make sure it looks correct.
Using Entities to Model Tables
At this point in the process you should have a complete logical model set
up and documented in a data modeling tool. Depending on the tool, you
may have the ability to translate what you have in the logical model into a
physical model.
If your tool doesn't have this option, you need to find another way to
document your physical model. You can model it directly in SQL Server by
using a database diagram, which would actually create the tables and other
objects, or you can set up another “logical” model but this time follow your
rules for physical modeling.
In our case, the tool supports both a physical and a logical model.
When we created our logical model, we used the tool to separate the enti-
ties into logical groupings called submodels. Submodels simplify working
with large data models, because they allow you to view a subset of entities
instead of 300 entities at once. The submodels we created are as follows.