Database Reference
In-Depth Information
still have a couple of submodels to look at, but our physical model is
quickly taking shape.
Web Session and Lists Submodel
Finally, we have two small submodels that we will look at together. The
Web Session submodel has only one new entity, Shopping Cart. This entity
acts as a join between Customers and Products so that customers can save
items in their shopping carts on the Web site for later purchase. This table
is simple. It holds the customer ID and the products that customers have
in their cart. In addition it tells us the quantity and the status of each prod-
uct. This entity is another simple table and is taken as is.
We also have the two tables used for lookup values: Lists and List
Items. These tables exist to provide the front-end application a place to
store related lists of data such as order status or various product attributes.
Again, these tables are physically modeled as is, with only name changes to
match our naming standards.
At this point we have looked at all the entities in the logical model and
found homes for them in the physical model. In the next section we look
at getting the relationships modeled in the physical world as well as getting
our primary keys in order and modeling columns based on the logical
attributes.
Using Relationships to Model Keys
While modeling our physical tables, we also took care of the primary keys
and the foreign keys. As you've seen, we named all the primary keys objid.
In the case of identifying relationships—those in which the primary key
columns on the table are also foreign keys—we name the columns after the
table they reference. Examples are the bin_objid and product_objid
columns in tbl_product_instance. Foreign keys, as you may have guessed,
are named in the same manner—the referenced table followed by “_objid,”
such as order_objid in tbl_order_details. This is a simple change; we go
through each table and rename these columns to match our standard.
Look at Figure 9.11 to see what the Orders submodel looks like with the
addition of the foreign keys columns. Each of the other submodels will
look similar after you finish renaming the columns.
The next thing we need to do is to ensure that the primary keys and
foreign keys conform to our naming standard. Now we are talking about
the actual constraints that will exist in SQL Server. Depending on your
Search WWH ::
Custom Search