Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
For 2021 - online Python 3 training - see ((here)).

Our plans were to retire in summer 2020 and see the world, but Coronavirus has lead us into a lot of lockdown programming in Python 3 and PHP 7.
We can now offer tailored online training - small groups, real tutors - works really well for groups of 4 to 14 delegates. Anywhere in the world; course language English.

Please ask about private 'maintenance' training for Python 2, Tcl, Perl, PHP, Lua, etc.
Mapping database rows to objects in a class


"I can see a parallel between a row in a table and an Object, but I'm not sure how one could relate to the other in programming terms. Do I simply load all my table rows into objects? And at what stage to I write them back out?"


Excellent question - I've not headed this section "answer" as there is no single answer of the "one size fits all" variety.

In a database, you're encouraged to store normalised (normalised) data - meaning that it should be split into a series of tables such that there is no duplication of information, no attempt to store multiple values into a single cell, and no storage of data that can be derived from a calculation based on other data stored. The logic behind these suggestions is good; it means you minimise storage space and improved data integrity and maintainability.

Within an object, chances are that you won't want to normalise your data. You'll want to hold a more complex structure in a single object, you'll want to hold calculated values, and some information may repeat from object to object. Furthermore, you're unlikely to want to load in all your data at the same time on a simple "row by row" approach.


Let's take an example - our library. At the time of writing, it comprises 5 tables in a MySQL database. The main table is a table of books, and there are separate tables of authors, publishers and subjects. That's because each book can have several authors and several subjects, and because each publisher is potentially responsible for more than one book. The final table is a table of book - to - author connections, necessary since each book can have multiple authors and each author can write multiple books.

Specification for a book object?? Let's try seeing how it might work out based on a sample usage.

Within each book object, I might hold a number of variables such as
   publication date
   image (.jpg) of cover
that comes from what I've described as the main table.

If I was writing an application that - let us say - reported the full details of one book and then a summary of others that were similar, I would probably start off by doing a quick SELECT go get all the (unique) ISBN numbers that apply, and titles and publication dates too perhaps, and I would store that information in a number of new book objects. An extra variable - let us call it
would be set to a value - let us say 1 - to indicate that the object had only been loaded to level 1.

From the information already loaded, methods called on my objects would reveal (say) books published in the last 12 months, and a program loop could be run to generate a web page to report on all books that were initially offered, with a full report on the recent ones. During this page generation, calls to a method such as getImage and getAuthors in the full reports would result in further database calls to load the information; the class's methods would know that these extra calls had to be made because of the setting of "loaded" to 1 when they were called, and once the extra information was loaded the variable would be set to a new value to indicate the new (more populated) state of the object.

With the author information, extra variables within the object would be arrays, so that the structure of the object would be much more complex that just the structure of a table row, author information would be repeated across a number of books by the same author, and calculated fields might be held too. That's fine; all these extras are hidden with ("encapsulated") within the object and accessed only via the accessor methods provided to the calling programmer - so they are NOT going to become a maintenance headache.


If you want to change an object that's derived from a database table using this model, perhaps in an application that reports on but doesn't alter many other objects, a good solution would be to add a variable with a name such as
to each instance of the class. Initialise the variable to 0, but change it to 1 whenever a "set" method is used that results in an object change that later needs committing to the database.

If you're using PHP5, Perl, Java, Python, then you simply provide a destructor method that will write the object information back to the database if the changed variable has been set to 1. In PHP4, keep an array of references to your objects and write a loop of calls to a "commit" method that has the same logic before your page exits

See also OO Design techniques

Please note that articles in this section of our web site were current and correct to the best of our ability when published, but by the nature of our business may go out of date quite quickly. The quoting of a price, contract term or any other information in this area of our website is NOT an offer to supply now on those terms - please check back via our main web site

Related Material

Object Orientation and General technical topics - Object Orientation: Design Techniques
  [80] - ()
  [236] - ()
  [507] - ()
  [534] - ()
  [656] - ()
  [747] - ()
  [831] - ()
  [836] - ()
  [1047] - ()
  [1217] - ()
  [1224] - ()
  [1435] - ()
  [1528] - ()
  [1538] - ()
  [2169] - ()
  [2170] - ()
  [2327] - ()
  [2380] - ()
  [2501] - ()
  [2523] - ()
  [2717] - ()
  [2741] - ()
  [2747] - ()
  [2785] - ()
  [2865] - ()
  [2878] - ()
  [2889] - ()
  [2953] - ()
  [2977] - ()
  [3063] - ()
  [3085] - ()
  [3260] - ()
  [3454] - ()
  [3607] - ()
  [3760] - ()
  [3763] - ()
  [3798] - ()
  [3844] - ()
  [3878] - ()
  [3887] - ()
  [3928] - ()
  [3978] - ()
  [4098] - ()
  [4374] - ()
  [4430] - ()
  [4449] - ()
  [4628] - ()

MySQL - Designing an SQL Database System
  [59] - ()
  [361] - ()
  [375] - ()
  [494] - ()
  [515] - ()
  [666] - ()
  [918] - ()
  [937] - ()
  [945] - ()
  [1423] - ()
  [1575] - ()
  [1771] - ()
  [2053] - ()
  [2085] - ()
  [2204] - ()
  [2749] - ()
  [3270] - ()
  [3361] - ()
  [3494] - ()
  [4426] - ()

resource index - MySQL
Solutions centre home page

You'll find shorter technical items at The Horse's Mouth and delegate's questions answered at the Opentalk forum.

At Well House Consultants, we provide training courses on subjects such as Ruby, Lua, Perl, Python, Linux, C, C++, Tcl/Tk, Tomcat, PHP and MySQL. We're asked (and answer) many questions, and answers to those which are of general interest are published in this area of our site.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2022: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/solutions/mysql-ma ... class.html • PAGE BUILT: Wed Mar 28 07:47:11 2012 • BUILD SYSTEM: wizard