Architecture-First Data Model to Database Schema
Discover more from Tony’s Technical Spot
Architecture-First Data Model to Database Schema
Overview
The Data Model was discussed in the 'Producing the Data Model' article. The Data Model is business focused and should be reflected further downstream in the application and the database.
Data Model
Data Model
The Data Model in the diagram above represents a diagram for the example Retail application. It is the result of business-level decisions during analysis.
Most of the entities are expected except for the Price and Discount entities. They have been created because of the dynamics and their importance to the application. Otherwise, they would have been an attribute on the Product entity.
For instance, a Product can have many Prices. At the same time, a Price can have multiple Discounts. These facts cause them to be first class entities on their own.
import com.google.gson.Gson;
import java.math.BigDecimal;
import java.util.List;
public class RetailExample {
class Discount {
private String reason;
private BigDecimal value;
//... other properties
}
class Price {
private BigDecimal value;
private List<Discount> discounts;
//... other properties
}
class Product {
private String id;
private List<Price> prices;
//... other properties
}
class Inventory {
private List<Product> products;
//... other properties
}
class Order {
private List<Product> products;
//... other properties
}
class ShoppingCart {
private List<Product> products;
//... other properties
}
class Customer {
private ShoppingCart shoppingCart;
private List<Order> orders;
private String customerType;
//... other properties
}
class Guest extends Customer {
String contactEmailAddress;
//... properties
}
class RegisteredCustomer extends Customer {
String userid;
//... properties
}
}
As shown above, a pure entity or class relationship is simple. The complexity is added when it is implemented in the supporting database structure as discussed in the following sections.
NoSql structure
The modern day format for NoSql data is JSON. In the past, the majority of NoSql data was stored in XML or CSV format. Today, JSON is a nice choice because it is simple, easy to process and ubiquitous across all layers of the application stack.
The NoSql strategy fits nicely with the Conceptual Simplicity philosophy as well as the Domain Driven Design approach. At this time, most databases support JSON, including MongoDB, SQL Server, Oracle, PostgresSql, MySQL and others.
An example of a supporting NoSql structure for a Registered Customer is shown below.
{
"userid": "customer1",
"customerType": "registered",
"orders": [],
"shoppingCart": {
"products": [
{
"id": "P0001",
"prices": [
{
"value": 24.99,
"discounts": [
{
"reason": "promotion",
"value": 2.00
}
]
}
]
}
]
}
}
Customer customer = new Gson().fromJson(jsonData, Customer.class);
Assuming the JSON data is loaded from the database, the application populates the corresponding objects. This is a simple process and generally executes quickly. After deserialization, the following simplified objects will be produced.
Loaded Objects
Some advantages of using the NoSql approach are
The data structure is simple and the simplicity is maintained in the database.
Most of the related data is together in one document so there is no need for the database to sift through possibly millions of records based on complicated joins
The same structure can be handled at all layers of the application
There is less need for transactions since most related data is stored in one pass and in one place
It has all the advantages of the XML approach without the extra weight
There is no need to manage schema changes across multiple databases
It is generally high performing
There is good support for scalability
As the support for NoSql database features increase, it is becoming the preferred approach for applications. The schema is not in the database, but is instead in the application, which makes applications easier to migrate to different environments. At a high level, the smarts are in the application and the database is focused on hosting data.
To be successful, it is important to have one way in, such as a Dao, and one way out of the database to ensure valid documents are stored. Also, the application should be backward compatible to handle older structures without error.
Relational Structure
The Relational database solution is the standard approach used for decades. Many developers are comfortable querying and modify data with SQL statements. But, many of the new developers are not comfortable accessing the database directly. Often, the database is hidden by components, such as Hibernate.
The Hibernate/JPA approach is nice, but it can be difficult to support and can sometimes have performance problems. The other problem is the handling of the Impedance Mismatch between the application’s concept of the data and the actual database structure.
An example of a potential relational database structure is shown below.
The database structure above is similar to the Domain Model except for a few main items
There is a relationship table called ProductPrices that associates Products and Prices.
The ShoppingCart is just a type of Order and is contained in the same table
Different types of Customers are differentiated by the customer_type column (not shown).
Creating the relational database solution has forced a deviation from the Domain Model. This is undesirable. One option is to leak these details into the application where the code has a ProductPrices model object or reference to the table in a SQL query or JPA @ManyToMany annotation. While this works it is not optimal and starts to limit the ability of the database to support structural changes. The application also becomes more complicated to understand.
The other option is to hide this deviation, in order to maintain Conceptual Simplicity. For this goal, there are a few approaches that can be applied
Use Views to hide implementation tables
A vw_Price view hides the ProductPrices table
Use Views to produce a conceptual entity
A vw_ShoppingCart view contains the filtered rows in the Order table that pertain to pre-orders
A vw_Order view contains the confirmed order rows.
vw_Guest and vw_RegisteredCustomer views contain corresponding rows, respectively
For inserting and updating data, the interface to the data should be a stored procedure. Therefore, the following stored procedures can be created among others
sp_Price_Insert, sp_Price_Update, sp_Price_Delete
sp_ShoppingCart_AddProduct, etc.
The combination of views and stored procedures hide the database implementation and maintain Conceptual Simplicity.
Finally
The Data Model is important and leads to the chosen database structure. The NoSql solution is quickly becoming the preferred approach, but a relational structure is still a good way to go. If a project team chooses the relational approach they should be careful to hide the technical details from the application. That is a process-level form of encapsulation.