DEV Community

Cover image for Simple database design for complex business model
Dan Mac
Dan Mac

Posted on

Simple database design for complex business model

One of the barriers of agile software architecture is the database schema changes. That is once the schema is set and data has been populated, it is very difficult to separate them, even they can separated, this affect the database layer or ever the business layer. Bugs and malfunction is unpreventable.

Here is what I have done to make a relational database design to be agile:

Simple database table for elements:

To define database tables as simple and small as possible. They should base on elements rather than business entities.
To define a table for storing an element and it is simple so we can decorate it later whatever we like. For example, if a person table is defined, the name fields should not be part of it. That means person and name are separated as elements and they are joint to be a business entity. You may think this is the normalization of the database and it probably true. But I think we should focus on elements which is purely abstraction.
Let's see how a girl got names. When she was born, she was known as a female at that time without a name yet. Then she was given a name. When she gets married, she would likely get another name, at least, the surname would be changed.

Let's define the schema of a person in such a way. A table called "person" with person id, DOB, status date and status. A table called "name" with surname, given name, other names, preferred name, start date, end date and status as well. A table called "person name relationship" with a start date and an end date and status. A table called gender with name, start data, end date and status etc.

Link elements together with relationships to form an entity:

To use the relationship to link the person to the name, the gender to form an entity "person" this time so the person entity can have many names and different gender. With this way of design, nationality, home address, emails, phone numbers and most everything can decorate the element "person" accordingly to be a colorful entity "person". Because we focus on elements, it is so flexible to meet the business requirement.

Consistence of data:

The status of the elements and the re-runnable process can enforce the data consistence. Check lists can be used to identify where the problems are if there is any.

Join elements with database views to form business entity interface:

To use database views to link elements together with their relationships to form entity interface for applications. The application only accesses data via views and it doesn't need to know how the database was designed and built. This does not just simplify coding, but also make data retrieval flexible because many views can be created accordingly and easily. The views also provide almost guarantee of the data consistence and data visibility.

To summarized above, to use decorating pattern to design the relational database.

Here is the example that I have developed. Any suggestions or opinions are welcome. Thanks.

https://github.com/squaressolutions/soms

Top comments (0)