I am looking for some advice on how to best design C++ programs that read, write, and update data in MySQL databases. I have completed a system that successfully does this but I'm not sure if I designed it the right way to take advantage of OOP principles and strengths.
The system I have built was something I wanted to manage my collection of woodworking articles from various magazines collected over the years. I wanted to query into my collection by keywords for all articles that would help me with a current woodworking project. For example, I can find all articles about building raised panel doors when I have a project to build a cabinet. I couldn't rely simply on a magazine's online or paper index because I have a collection of various mags that I have been receiving over many years and I inherited some from my father.
Some background: I am a retired IT tech professional. I started as an IBM mainframe systems programmer and became a fair programmer in 370 assembler, SAS, and some procedural languages. When I retired, I wanted to keep learning and have done a few things in PHP, Perl, and lately, C++. All of this work is done under and for Linux.
My woodworking index system is written in C++ with a MySQL data store. I wrote it as a GUI using Gtk. I have it working but feel I could do a better job (ie, tighter code and more in line with recommended programming guidelines for C++).
I'm looking for advice on how best to design a OOP using C++ with MySQL. I have tables for mags, issues, articles, and a couple of classification tables (ie, indoor furniture, outdoor projects, etc.). I tried to design the database with the highest level of normalization I could achieve so the tables are linked by foreign keys and I've defined referential integrity rules.
If you are an experienced C++ designer and coder, should I build a class for each table entity (ie, a mag class, an article, etc)? Would each then have a 'search' method? How do you deal with linking across tables (ie, an SQL join)? Is there a method in each class to return one record/row of the table until the result set is exhausted? Any suggestions, or links to books or articles to help me with this would be greatly appreciated.