Building a Database-driven Application Framework

Normally, people use database as data store, which contains all data tables and lookup tables. The front end application reads the data, presents them to user. If we get another similar request, we simply build another application, which handles another similar database schema. If we get a dozen clients, it’s going to be hard to maintain them all.dbdriven

Many application vendors are struggling to manage the variety of clients’ requirements, while providing similar functional modules to different clients. How do we handle all these changes easily on a common front-end code base? There are many answers, you might say that we may encapsulate the modules, inherit them and expand them. It’s a valid answer. But if we may manage all these changes in database, remain application code base unchanged, it may improve the visibility of changes, speed up deployment procedure and reduce the cost significantly.

A live example is WordPress site. It manages all artifacts through database and it’s a database-driven application completely. Let’s see how we reach our goal through a nicely designed data model.

The key to build a database-driven application framework is to build a structured database schema. We are not only using database as a pure data store, but also using it as a controller. A structured database schema is built based on well-designed name convention. Except data tables and lookup tables, we also need a set of control tables which manage the metadata of all data tables, their referential relationships, e.g., which data table column refers to which lookup code column.

Whenever we add new functional modules, we use new name convention, create a new set of data tables. Once we done, we register all the newly added data table names, column names, display names (configurable), default values (configurable), etc. in control tables.

Instead of reading data tables directly, our front-end application reads from control tables. Since the structure of control tables is fixed, no matter what new modules we need, we don’t need change front-end code base. All we need do is to add a new set of data tables and relevant lookup tables, register them to control tables, and let front-end application extract and present data from control tables. That’s all. There’s no need to change the code base of front end application.

The database-driven design can be easily applied to any vertical industries, such as forestry, utilities and energy, etc. Our consultants have designed and deployed over a dozen database-driven applications. We got a 48-page thesis on this and would like to share with you.

Should you have any questions on designing high quality data model to support database-driven application, please feel free to contact us. For more relevant information, please access Data Architecture, Data Modeling, Data Analysis & Migration, Data Warehouse & BI sections.