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
