Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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
add comment

4 Answers 4

up vote 12 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
5  
+1 for not everything must be OOP (even if some ORMs are quite nice!) –  Javier Dec 7 '10 at 19:57
1  
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
1  
@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 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
show 2 more comments

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
add comment

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
add comment

ORMs are made for people who don't know SQL and database design.

/me ducks

share|improve this answer
2  
oh ho, not true! It means we don't want to write the same DB layer over and over again. –  BlackICE Dec 7 '10 at 20:07
1  
You were just asking for a downvote (it's not even an attempt to answer the OP's question), so I obliged. –  qes Dec 7 '10 at 20:24
1  
I will refer to this: secure.wikimedia.org/wikipedia/en/wiki/… and say that a good database access library (such as python's) lets you run statements safely on a database with few lines of code. –  Christopher Mahan Dec 9 '10 at 22:29
1  
ORM's are made for people who understand the time / space tradeoffs between using an ORM and handwriting SQL queries / prepared statements and know it's more efficient to use an ORM in 90+% of cases. –  Josh K Apr 28 '11 at 21:08
1  
ORMs are leaky. –  Christopher Mahan Apr 28 '11 at 21:41
show 4 more comments

Your Answer

 
discard

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.