Database – Best Practices: Database app programming patterns

database

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

Best Answer

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.

Related Topic