Database Design via Use Cases
By Nalla Senthilnathan, nsenthil@hotmail.com

Introduction: Traditionally data modeling is performed by considering the requirements en masse. In this weblog I present an alternative approach to data modeling by first writing the use cases and then identifying the subsystems and components and finally identifying the database tables. The database tables are identified incrementally on a per-component basis.

Sample Requirements, Use Cases

Let us say we are required to design database tables for an online auction system. The use cases for this system are given to be:

1. user -> ( view auction item catalog ) << include >> ( search auction items ), ( view auction item list )
2. user -> ( register )
3. member -> ( update profile ) << include >> ( login ), ( view profile )
4. member -> ( post item for auction ) << include >> ( login )
5. member -> ( place bid ) << include >>  ( login ), ( view auction item )
6. auctionHouseAdmin -> ( close auction ) << include >> ( view closed auctions ), ( notify buyer ), ( notify seller ), ( debit seller account )

Subsystems

Following the pattern suggested in [Ref. 1.] we proceed to identifying the subsystems and components. We first identify possible subsystems by grouping related use cases. Clearly, the above use cases can be grouped into the following subsystems:

CatalogApp [1]
- viewAuctionItemDetails()
- searchAuctionItems()
- viewAuctionItemList()

RegistrationApp [2,3]
- register()
- login()
- viewProfile()
- updateProfile()


AuctionUserApp [4,5]
- login()
- postItemForAuction()
- viewAuctionItemDetails()
- searchAuctionItems()
- viewAuctionItemList()
- placeBid()

AuctionHouseAdminApp [6]
- closeAuction()
 

where I have also indicated the services that the corresponding apps expose to their clients. These services are identified directly from the corresponding use cases.

Subsystem Services

We then identify the services that enable the subsystems to fulfill their goals. The subsystem services can be identified by writing event flows for all the use cases. However, for the sake of brevity, I'll skip the event flows and write the services required by the apps directly as:

CatalogApp
- getAuctionItemsForCriteria()
- getAuctionItemDetails()

RegistrationApp
- createUser()
- createAccountForUser()
- isValidUser()
- getUserDetails()
- updateUser()
- updateUserAccount()

AuctionUserApp
- isValidUser()
- addItemForUser()
- createAuctionForItem()
- getAuctionItemsForCriteria()
- getAuctionItemDetails()
- addBidForAuctionItem()

AuctionHouseAdminApp
- getClosedAuctions()
- setWinningBidForAuction()
- debitSellerAccount()
- notifyBuyer()
- notifySeller()
 

Please note that the services indicated for each app are not the methods of the corresponding apps! They are the services required by the apps to fulfill their client's goals.

Components

We now group the services identified in the previous step based on 'Separation of Concerns' principle and identify the following components:

UserMgr
- createUser()
- getUserDetails()
- updateUser()
- isValidUser()

AccountMgr
- createAccountForUser()
- updateUserAccount()

BidMgr
- addBidForAuctionItem()
- getBidsForAuction()

AuctionItemMgr
- getAuctionItemsForCriteria()
- getAuctionItemDetails()
- createAuctionForItem()
- addItemForUser()
- getClosedAuctions()

AuctionHouseMgr
- setWinningBidForAuction()
- debitSellerAccount()

MessagingMgr
- notifyBuyer()
- notifySeller()

All the components identified above with their dependencies are shown in the diagram below.

It is interesting to note that the above component diagram is in good agreement with the "Business Interfaces" and "System Interfaces" concept mentioned in [Ref. 2].

Refactoring Component Dependencies

To avoid one subsystem accessing components of another subsystem directly, we could create helper components that encapsulate the knowledge of invoking methods on another subsystem. The refactored component diagram would then look like:

Database Tables

We can now identify database tables on a per-component basis as follows:

The methods in UserMgr component suggest that we need a table like:
USER [ userid, password, userdetails ]

The methods in AccountMgr component suggest that we need a table like:
USER_ACCT [ acctid, userid, creditcardnumber, accountDetails ]

The methods in AuctionItemMgr component suggest that we need tables like:
ITEM [ itemid, userid, itemdetails ]
AUCTION [ auctionid, itemid, startprice, startdate, closedate ]

The methods in BidMgr component suggest that we need a table like:
BID [ bidid, auctionid, userid, bidprice, biddetails ]

The methods in AuctionHouseMgr component suggest that we need tables like:
AUCTIONHOUSE_TRANSACTION [ transactionid, bidid, acctid, debitamt, transactiondate ]

Domain Model Diagram

Having identified all the tables we can now draw our Domain Model Diagram as shown in the figure below.
 

Summary

Since use case analysis is anyway performed in a software developmemt process, the proposed approach adds only an incremental additional effort in identifying the database entities. The approach is shown to enable identifying not only the entities but their relationships and attributes. Since the domain model is discovered in small steps on a per-component basis, the whole process is less daunting than the traditional approach where the complete domain model is discovered en masse considering all the requirements at one go. The proposed approach is therefore expected to be less daunting compared to the traditional approach in developing data models for projects with a large set of requirements.

References

1. A Component Identification Pattern by Nalla Senthilnathan, 2003

2. From Requirements to Components by John Daniels, 2001


© Nalla Senthilnathan, 2003