Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have written many database (MySQL) web apps so far but I always think my structure is kinda clumsy. I want to improve the programming/design pattern I use, hoping for some advice here. Particularly, I cannot find a structure that complements an OOP approach that encapsulates the implementation of the database (schema). I

Think my question can be best explained by example. There are 2 approaches I use now say I have an Invoice object/class:

first is to use static member functions

class Invoice
   int id;
   string ref;
   int customer_id;
   date created;
   date due;

   static id create();
   static bool update(id, field1, field2, ...);
   static bool delete(id);
   static bool get(id);

The second approach is to put every thing in a database object:

class Database extends ProprietaryDBConnecter, Singleton
   id createInvoice();
   bool updateInvoice(id, field1, field2, ...);
   bool deleteInvoice(id);
   bool getInvoice(id);

   id createCustomer();
   bool updateCustomer(id, field1, field2, ...);
   bool deleteCustomer(id);
   bool getCustomer(id);

   // etc...

I find that both ways the (SQL) member functions are very much unseparable from the "view", in that the "view" determines what the classes needs to have and hence seems to break the document/view architecture.

Also, it seems kind of inefficient for example a SELECT statement should only select what is needed, but the presence of member variables in Invoice seems to imply "guaranteed data".

Don't know if I explained the question clearly, What are some other best approaches to this architecture/design pattern/what-it-is-known-as?

Thanks for advices

share|improve this question
up vote 13 down vote accepted

Well I suppose that you could use an ORM.

But really, database design should NOT follow OOP priciples, it should follow database design priciples such as normalization. And it should be designed at the database not in the application. And the data integrity rules should be enforced at the database level not by the application.

I would suggest you read some database design books and, then, read about performance tuning the database of your choice.

share|improve this answer
+1 for not everything must be OOP (even if some ORMs are quite nice!) – Javier Dec 7 '10 at 19:57
O/RMs are nice, but using them doesn't mean you can't conform to good database design. – BlackICE Dec 7 '10 at 20:06
@David, I agree that is true in the hands of someone who knows what he is doing. And I did suggest he look at an ORM, but really if you don't know database design first, an ORM is a dangerous tool. – HLGEM Dec 7 '10 at 20:20
That's a good point that data integrity rules can be enforced at the database level. However, sometimes it makes sense to put some rules (such as 'project must always contain more than 5 team members') in the application if the rules are subject to change, and only the application will be modifying data. – Jon Onstott Dec 7 '10 at 21:13
The application is almost never the only thing that modifies data. Business rules not put into the database tend to very easily get violated when someone needs to make a quick change to a large chunk of data to support some data fix. – HLGEM Dec 7 '10 at 21:18

I cannot find a structure that complements an OOP approach that encapsulates the implementation of the database

Seems like you're describing the Object-relational impedance mismatch.

There are number of things that are supposed to solve this OODBMS, ORM tools, a host of data access tools.

I think the fact that there's so many solutions leads me to believe that One True Solution™ doesn't exist.

So you can pick any direction you like secure in the knowledge that some people will hate it and some will love it.

share|improve this answer
Looks like it is, in a more rigourous way. – Jake Dec 7 '10 at 22:27

If you don't want to use an ORM wrapper, use a database that supports OOP style storage like MongoDB.

MongoDB (from "hu​mongo​us") is a cross-platform document-oriented database system. Classified as a "NoSQL" database, MongoDB eschews the traditional table-based relational database structure in favor of JSON-like documents with dynamic schemas (MongoDB calls the format BSON), making the integration of data in certain types of applications easier and faster...

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.