Web Dev

Good Design Practices: Database Patterns


Today i`ve decided that i should come up with best practices for development in general. I have found these design patterns to solve most of the problems i face during either web development, or plain desktop development and it’s the best if you learn and follow them, for better, structured and logical code.

Questions and Answers Pattern: Solution to related tables

If you’ve ever wrote a forum, you might already know this pattern. You create a column in both of the related tables, and save the same variable for each of the related rows, let me give you an example:

You have a table named: forum_questions (Which are the posts),with the following columns:

question_id question_title question_content question_poster

And another table named: forum_answers, with the following columns:

answer_id question_id question_title question_content question_poster

See the pattern here? In order to find all the related answers to that question (meaning finding all the replies to the post), You just have to save the id for the post you want, and look for all rows in forum_answers matching that question_id, making your job much easier.

A few days ago, i was stuck in writing a voting system for a client using Ajax and CodeIgniter. Thanks to this pattern, i have wrote the 30 model methods for getting / setting variables in the database, just in a hour. Speaking about models…

Business Model Design  Pattern / Abstraction Layer Design Patern

Thanks to OOP we have been introduced to code that looks like:


OOP makes it possible to create code which is much more easy to understand and makes loose coupling (which is a very important feature in all applications, whether web applications, desktop applications, or plain microcontroller code). Plus makes it possible for dynamic instantiation so you don’t have to load all your components where you don’t need them.

In OOP, you can define an abstract or concrete object, like votes, forum posts, or comments, or bricks, shovels, shoes (whatever you sell in your site mostly) and assign different states (like disabled or enabled for posts, out of stock or in stock for shoes etc.), and assign different methods like shoe.change_color(), brick.throw_at_wall() etc.

So here comes the big question, which is, “Why don’t we apply this to database in general?” . In order for maximum readability, you should add an extra layer of abstraction to your basic database functions.So let’s see a few examples of that:

Basic Database Functions:





Abstraction Layer For Votes:





Abstraction Layer for

From what i have seen on other Model View Controller frameworks, or just new PHP frameworks in general, is that they always re-inforce this concept, through models. As a programmer, you should always create separate controllers, for separate object, like votes, forum posts, user comments, and every kind of input which needs storage (except for plain HTML, that’s just too try hard) . So make sure that you follow these almost religiously, and i can bet that it will solve every database-related problem while programming your application! (and you’ll lose that bet too!)

Really Cool JQuery Plugins For You to Use

Previous article

Good Design Practices: MVC Frameworks with Ajax

Next article

You may also like


Comments are closed.

More in Web Dev