Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
Python and Tcl - public course schedule [here]
Private courses on your site - see [here]
Please ask about maintenance training for 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
  [4628] Associative objects - one object within another. - (2016-01-20)
  [4449] Spike solution, refactoring into encapsulated object methods - good design practise - (2015-03-05)
  [4430] The spirit of Java - delegating to classes - (2015-02-18)
  [4374] Test driven development, and class design, from first principles (using C++) - (2014-12-30)
  [4098] Using object orientation for non-physical objects - (2013-05-22)
  [3978] Teaching OO - how to avoid lots of window switching early on - (2013-01-17)
  [3928] Storing your intermediate data - what format should you you choose? - (2012-11-20)
  [3887] Inheritance, Composition and Associated objects - when to use which - Python example - (2012-10-10)
  [3878] From Structured to Object Oriented Programming. - (2012-10-02)
  [3844] Rooms ready for guests - each time, every time, thanks to good system design - (2012-08-20)
  [3798] When you should use Object Orientation even in a short program - Python example - (2012-07-06)
  [3763] Spike solutions and refactoring - a Python example - (2012-06-13)
  [3760] Why you should use objects even for short data manipulation programs in Ruby - (2012-06-10)
  [3607] Designing your application - using UML techniques - (2012-02-11)
  [3454] Your PHP website - how to factor and refactor to reduce growing pains - (2011-09-24)
  [3260] Ruby - a training example that puts many language elements together to demonstrate the whole - (2011-04-23)
  [3085] Object Oriented Programming for Structured Programmers - conversion training - (2010-12-14)
  [3063] Comments in and on Perl - a case for extreme OO programming - (2010-11-21)
  [2977] What is a factory method and why use one? - Example in Ruby - (2010-09-30)
  [2953] Turning an exercise into the real thing with extreme programming - (2010-09-11)
  [2889] Should Python classes each be in their own file? - (2010-07-27)
  [2878] Program for reliability and efficiency - do not duplicate, but rather share and re-use - (2010-07-19)
  [2865] Relationships between Java classes - inheritance, packaging and others - (2010-07-10)
  [2785] The Light bulb moment when people see how Object Orientation works in real use - (2010-05-28)
  [2747] Containment, Associative Objects, Inheritance, packages and modules - (2010-04-30)
  [2741] What is a factory? - (2010-04-26)
  [2717] The Multiple Inheritance Conundrum, interfaces and mixins - (2010-04-11)
  [2523] Plan your application before you start - (2009-12-02)
  [2501] Simples - (2009-11-12)
  [2380] Object Oriented programming - a practical design example - (2009-08-27)
  [2327] Planning! - (2009-08-08)
  [2170] Designing a heirarcy of classes - getting inheritance right - (2009-05-11)
  [2169] When should I use OO techniques? - (2009-05-11)
  [1538] Teaching Object Oriented Java with Students and Ice Cream - (2008-02-12)
  [1528] Object Oriented Tcl - (2008-02-02)
  [1435] Object Oriented Programming in Perl - Course - (2007-11-18)
  [1224] Object Relation Mapping (ORM) - (2007-06-09)
  [1217] What are factory and singleton classes? - (2007-06-04)
  [1047] Maintainable code - some positive advice - (2007-01-21)
  [836] Build on what you already have with OO - (2006-08-17)
  [831] Comparison of Object Oriented Philosophy - Python, Java, C++, Perl - (2006-08-13)
  [747] The Fag Packet Design Methodology - (2006-06-06)
  [656] Think about your design even if you don't use full UML - (2006-03-24)
  [534] Design - one name, one action - (2005-12-19)
  [507] Introduction to Object Oriented Programming - (2005-11-27)
  [236] Tapping in on resources - (2005-03-05)
  [80] OO - real benefits - (2004-10-09)

MySQL - Designing an SQL Database System
  [4426] FileMaker Day to Unix Time conversion - (2015-02-15)
  [3494] Databases - when to treat the rules as guidelines - (2011-10-23)
  [3361] Blowing our own trumpet - MySQL resources - (2011-07-18)
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [2749] Delegate Question - defining MySQL table relationships as you create the tables - (2010-05-02)
  [2204] Images in a database? How big is a database? (MySQL) - (2009-05-28)
  [2085] MySQL - licensing issues, even with using the name - (2009-03-16)
  [2053] What a difference a MySQL Index made - (2009-02-25)
  [1771] More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24)
  [1575] Database design for a shopping application (MySQL) - (2008-03-15)
  [1423] MySQL - table design and initial testing example - (2007-11-06)
  [945] Code quality counts - (2006-11-26)
  [937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
  [918] Databases needn't be frightening, hard or expensive - (2006-11-08)
  [666] Database design - get it right from first principles - (2006-04-02)
  [515] MySQL - an FAQ - (2005-12-03)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [375] Oops - I got my initial database design wrong - (2005-07-12)
  [361] Binary Large Objects or bars - (2005-06-27)
  [59] MySQL - Pivot tables - (2004-09-22)

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., 2019: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01225 708225 • FAX: 01225 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