Assem Bayahi on Database Design

Answers to database design questions by Assem Bayahi, Mourouj 1, Tunisia
Date: 30 December 2014

Please explain (or briefly describe) the general philosophy behind your database design approach.

     Generally, I try to organise projects in an MVC pattern, so the Database is designed to respects Models logic with focusing on avoiding redundancy.

Where do you live?

     Tunisia

Briefly describe your pertinent work and/or educational background.

     I have a master degree in Computer programming applied to managment (Informatique appliquée à la gestion). Since 2003 I'm working with Audaxis (www.audaxis.com): I started as a developer and I'm now a technical director. I worked for the last seven years mainly on web projects but now I'm also working as the team leader of the OpenERP (odoo) tunisian Team. For more details: http://tn.linkedin.com/in/bayahiassem/en

What are the industries you have the most experience working within?

     Press/Media (Mobile Applications) and NGO (Web applications).

Your rate is higher what other programmers and freelancers charge. How does what you do with database design save clients and companies money in the long run?

     Well, it's not really the case because I'm not mainly a DB master. This is said, designing a good database, specially when dealing with big data provide those advantages:
     * Preventing database size explosion by avoiding redundancy: no need for big servers and no need to spend money on server capacity extension.
     * Good response time by using Indexes and views...: no need to spend money on fast processor. Time is money.
     * Delay software scalability problems. No need to spend money trying to enhance software.
     * Make it easy to expand and maintain software: a clean design make it easy to add module and tables.

You have special expertise in database design. How is what you do different from what a generalist computer programmer might do when it comes to creating a database back end?

     A database designer must aim for the future.

When you look at database design work done by generalist programmers or other people with limited DB design expertise, what is the most insane or annoying thing you see repeatedly?

     Non use of database options that organise data and enhance database performance: indexes, constraints, views, procedures, column sizes. In general, a generalist programmers don't give importance to those details and focus only on preparing the database skeleton.

How is the database design work you do distinct from and also integrated with other aspects of overall software/technology systems?

     I think that it's necessary to focus on database design, but it's also needed to collaborate with developers because sometime, the database design must be modified to help make it easy for the developer to go forward.

Describe how the database design work you do now is similar to or different from "textbook database design," such as one might learn in college or read in a text book or software manual.

     Some basics learned in college are essential: like how to define tables relations (one_to_many, many_to_many,...) or how to avoid redundancy. Those basics are very useful when designing database. What is different is that by experience you know really how those choices and how details influence the database performance.

What is the biggest example of a "rule" you routinely break? (Or another way of putting it is: what popular tradition are you most likely to depart from, if any?)

     Generally none, but sometimes you are obliged to save the same data in two different places.

What RDBMS have you used extensively, or are at least somewhat familiar with based on first-hand experience?

     MySQL

Given an assignment to create a website from scratch for Company X, what RDBMS are you using? (website specs: principle table has 10,000 rows and will grow to 200,000 over next five years; admin back-end, general browsing, user logins; no more than 100 concurrent users)

     We have already done some sites with similar specs and we used MySQL witch is often used with websites/web applications. If configured well (right log level, clean data, enabled cache,...), MySQL is good enough.

What other technologies are you using for this website?

     PHP, I like Symfony (I worked with version 1.4 not 2): it's a great MVC framework (well, if not used carefully, we can have some performance issues because of it's excessive use of Objects). Drupal also is an option (even if I don't like the fact that it's not really OO cms).

What tools would you be using? (IDEs, code editors, database design tools, if any)

     Eclipse is my preferred one because you have a plugin for everything. SVN or GIT for versioning. When I was on Windows, I liked using Toad For MySQL for database work. On ubuntu, MySQL Workbench is not bad. Sublime Text is also an option.

Aside from the database design, how much other development do you typically do? How much would you prefer to do?

     I like developing the application logic and organizing it's architecture but I like also debugging and enhancing others code. The only thing that I don't enjoy doing is the Design and CSS integration.

Table names: plural? singular? or something else? why?

     Sometimes, mainly when working with a framework or a cms, you are obliged to respect the framework rules. But if I have the choice, based on the MVC logic, I prefer Singular names.

How do you name your primary identity fields?

     I like the simple and significative name ID : it's « universal » and anyone how see it will know directly that it's the primary key.

Describe your relationship (if any) with WordPress?

     I have never used it, but I'm considering taking a look at it: it seems a very popular CMS.

If you use stored procedures, how do you decide to use them or not use them? What are your alternatives?

     Well, lately, another client asked me, based on GPS data, to list all the trips taking in consideration the car speed and other conditions: well, because I have to extract « parallel » data from one table (list of start - end), I directly decided to use a stored procedures. For me it's simple, if I can't use SQL (need for a loop or some complicated calculations) then a procedure is the solution.

Why would a client who needs database design work done be making a wise move to hire you (if you're available)?

     Well, four things: My search for perfection, My analytical capacity and Most of all my capacity and my desire to learn new things (all that I have learned from the start of my career, I learned it by myself: php, symphony, cakephp, codeIgniter, Titanium, GWT, Tapestry, Maven, GIT, SVN, Ruby, Python, OpenERP...). The fourth thing is my frankness : If I know I can't do the job, I will say it loudly :)