Logical diagrams are to convey requirements only. Physical diagrams represent the actual data structure to support the requirements and take into account technical scalability and speed.
Edit: I hate the way I had to format this document for this blog post. If you want this tutorial better formatted, check out the Word document.
One-to-Many Relationship
Logical
On ER/Studio, two tables are created. The Store table has a primary key StoreID. The Manager table has a primary key, ManagerID and a foreign key, StoredID (which is mapped to StoreID from the Store table).
Physical
On SQL Server, two tables are created. The Store table has a primary key StoreID. The Manager table has a primary key, ManagerID and a foreign key, StoredID (which is mapped to StoreID from the Store table).
Manager Table:
If you allow NULLs for StoreID in the Manager table, then you’ll be able to have a Manager without a store. If you don’t allow NULLs (leave it unchecked), then you’ll have to have at least one Store assigned to a Manager.
Querying
The above states that one store can have many managers. Here’s some sample data what’s in the tables:
<div align="center"><br />
<img src="http://www.shinylight.com/wp-content/uploads/2010/01/image006.jpg" width="240" height="171" />
</p>
</div>
<p>
<div align="center"><br />
<img src="http://www.shinylight.com/wp-content/uploads/2010/01/image007.jpg" width="414" height="77" />
</p>
</div>
<p>Get all manager information with for all managers that belong to a store:</p>
<table width="100%" border="0" cellspacing="5" cellpadding="3">
<tr>
<td>SELECT Manager.ManagerID, Manager.FirstName, Manager.LastName, Manager.StoreID,
Store.[Name], Store.Address, Store.State, Store.City, Store.Zip
FROM Manager
INNER JOIN Store ON Manager.StoreID = Store.StoreID
|
|
Get all manager information with for all managers (even if they don’t have a store):
SELECT Manager.ManagerID, Manager.FirstName, Manager.LastName, Manager.StoreID,
Store.[Name], Store.Address, Store.State, Store.City, Store.Zip
FROM Manager
LEFT OUTER JOIN Store ON Manager.StoreID = Store.StoreID
|
|
|
Notice the NULL for Steamboat Willie. He doesn’t have a store, so all Store related fields show as NULL.
Many-to-Many Relationship
In order to implement this physically, you need a join table. In this case, we use StoreManager. Logically, you only need only two entities (Store and Manager).
Logical
Physical
On SQL Server, three tables are created. The Store table has a primary key StoreID. The Manager table has a primary key, ManagerID. The table StoreManager has two foreign keys: StoredID (which is mapped to StoreID from the Store table) and ManagerID (which is mapped to the ManagerID from the Manager table).
StoreManager Table:
If you allow NULLs for StoreID and ManagerID in the StoreManager table, then you’ll be able to have a Manager without a store. If you don’t allow NULLs (leave it unchecked for both), then you’ll have to have at least one Store assigned to a Manager.
Here’s some sample data what’s in the tables:
<p align="center"><img src="http://www.shinylight.com/wp-content/uploads/2010/01/image016.jpg" width="157" height="140" /></p>
<!--start_raw--><br /><!--end_raw-->
<p align="center"><img src="http://www.shinylight.com/wp-content/uploads/2010/01/image017.jpg" width="331" height="65" /></p>
<!--start_raw--><br /><!--end_raw-->
<p align="center"><img src="http://www.shinylight.com/wp-content/uploads/2010/01/image018.jpg" width="73" height="204" /><br />
</p>
<!--start_raw--><br /><!--end_raw-->
<p>Get all manager information associated with his store:</p>
SELECT Manager.ManagerID, Manager.FirstName, Manager.LastName,
StoreManager.StoreID, StoreManager.ManagerID, Store.StoreID,
Store.[Name],Store.Address, Store.State, Store.City, Store.Zip
FROM Store
INNER JOIN StoreManager ON Store.StoreID = StoreManager.StoreID
INNER JOIN Manager ON StoreManager.ManagerID = Manager.ManagerID
ShinyLight Blog 