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,
Best Answer
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.