Organise C++ classes around SQL database

Question

My question is about how best to organize C++ classes around a database model, and I understand this may appear very elementary.

The software I propose to create will do the following. It is intended for small FMCG manufacturers and will use Qt.

• The software will record non-conformance's, product complaints, corrective actions and serve as an electronic record keeping
• There will be quite a bit of 'business logic', as the standout feature is intended to be comprehensive reporting, searching and analysis, as well as a flexible approach to entering data to smooth workflows (ie, a break away from the "one screen per process stage" paradigm)
• The persistent store is an SQL database, with a table(s) for each type of record (some records will be broken into separate tables for investigations/solutions. This means that most work will be done on a single row).
• Each customer who uses this software will require amendments, additional columns/fields or changes in column names and maybe business logic. Each customer may require updates which change the database structure. The idea is that anything that can be customized, any client specific requirements will live within these classes.
• There will be reporting, mainly involving pulling records matching particular categories, word searches, etc.

I have the database schema done and laid out, and the logic of how tables relate is clear. I have used this very schema in another framework, but a web based app doesn't seem to offer the level of power to the user with regards to the GUI, which is essential to differentiate this product from the others.

My question is how to design classes around the database. It is important to get this right, so that the architecture of the software doesn't have to be redesigned later on. The classes will not be responsible for the persistent store, only for acting as an intermediary between the layer responsible for the persistent store and the controllers/views.

The most logical approach appears to be to have a class for non-conformances/complaints etc, which solely store the instance (ie, one object per non-conformance record), validate data and class specific business logic (ie, computations). One class per table seems logical in this case. What I'm struggling with, is whether it is standard practice to simply have data members for each column, define getters and setters, or whether I should create a class which isn't hard-coded to map to a table, but which populates a map based on the database schema or external file which defines the database and column properties. The former means that the coding for the class needs to be changed when columns are modified, the latter potentially not. Keep in mind, for the majority of the time, each user will be working with one record at at time.

In short, I can see example of how to do SQL queries, but I've struggled to find anything which shows me what the class should look like (or if this entire approach is wrong) and I'd like to compare what I'm doing, with what the industry would define as good practice (I'm not a programmer by trade).

Thank you,

Answer 1

I recommend having one class per table, and one field per column. This pattern is widely used by Object Relational Mappers (ORMs) in many languages, and it works very well. These classes form your domain model. One issue, as others have noted, is that if you want to change your data schema, you need to change application code. If that is an issue for you, you may want to consider semi-structured data (e.g. JSON columns, or even NoSQL).

You can put some logic in your data classes, but I tend to keep this quite lightweight. And methods on a data class should only operate on that class. For more involved business logic, and anything that involves more than one class, create a service layer. The service layer offers an API to the rest of your application, and depends on the model.

By the way, it is quite unusual to write this kind of software in C++. Usually higher-level languages (C#, Java, Python, etc.) are more suitable.

Answer 2

The common mistake many people make with database mapping is that they focus on the schema and develop a tightly-coupled representation of the tables in business logic classes. This is not the best way at all - obviously it makes the business logic layer so tightly coupled to the DB that they quickly realise its a mistake and end up adding another layer to try to decouple business logic from DB, calling it a DAO or ORM or similar.

What you should do is look at your DB as a layer in itself, one that provides an API where data requests are made. I find its easiest to conceptualise this by writing all your SQL as stored procedures and restricting all access to the underlying tables. (this approach also has the benefit of increasing security)

Once you do this, you start to look at the data as a resource to be consumed, the business logic will have classes written that map to the stored procedures and they work together - logic in the business logic classes with data that is operated on sourced from your "data API". It also means you can change the schema without any change to the business logic layer at all (though, obviously if you change the stored proc API that is used, you'll have to change the callers, but you can add new sprocs where needed without any impact on existing code).

Another benefit is that your DB and your business logic are then independent and can be worked on separately. using your DB API would be not much different than using a 3rd party API provided via a web service or other data source.

Answer 3

The first question is if you are okay with having to recompile the program every time the database schema changes. I'm not quite clear on your situation but for example:

You have a number of clients with slightly different needs (ie A needs to track Properties X, Y, Z, but B also needs to track Property W) but for a given client those needs are pretty constant. And you can work with and deploy a custom application to each client (aka you aren't selling this off the self). In that case, have a template that you can then inherit from and modify on a per client basis might be a pretty good business strategy, if the total number of clients is not high enough to make it worth creating a highly adaptable application right from the start. Having 5 different versions of the program is still maintainable, 100 is not.

On the other hand, if you have 100 clients you simply cannot be changing the code every time one of them wants to add a property (column) to something. Or even add a new type of object. So the program will need to be more dynamic, probably working off a relational map that is saved somewhere and that can be edited by the program as needed.

So lets assume what you have is more like the former. Having classes that represent the types of records you have is a good way to do it. Those classes can contain SQL queries directly. You should still however have things like table names as constants defined in a common .h file and then call them as constants in your actual code so any change needs to be made in only one place. Then if you need a slightly different object for a different client, you can inherit the classes you already have and make the necessary changes.

You could also put together an intermediary class that handles actual database interactions, so if you change the schema or even the type of database you are using, you just need to switch out or modify that class.

Answer 4

First, for me the most natural way of approaching projects like this is first design the domain model and only after that design how that model is implemented in the selected programming language or how it's stored in a relational database.

Second, since each customer will require amendments, additional columns and customized column names, I would forget the idea of storing each record on a single row and instead use the entity attribute value (EAV) model or even consider using a document oriented database instead of a relational one. It should be noted though that some people consider the EAV model as an anti-pattern because it will cause some additional complexities.

Finally, I'm guessing that all records will still have some shared subset of core attributes that are same for all customers. To me the cleanest approach would be to separate this core part from customer specific extensions altogether. The core attributes I would keep as data members in your C++ classes for ease of use and design a separate mechanism for the extensions. Also, if you decide to use something in the likes of the EAV model, I would still keep these core attributes in dedicated columns instead of splitting them to their own rows.