Ven Grollmus on Database Design

Answers to database design questions by Ven Grollmus, George Town, Tasmania, Australia

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

     My approach is to understand the company I am working for, and to document what they wish to achieve from the system. This ensures that there is little chance of something being missed in the development phase of the build as the workflow has been fully thought out and documented, with all the relevant information that needs to be captured has been thought about.

What is something you have seen done repeatedly in database design work done by others people which you find to be particularly annoying or detrimental?

     I have found the lack of naming conventions and poor normalisation of tables very detrimental to systems, as this makes it hard for new support staff to come in and support an application and to find issues when something isn't quite working as expected.

Given that you are database specialist, how does what do you while creating your database architecture save clients time and/or money?

     I spend time with the client at the outset to understand what they need to achieve with the system, document the processes and requirements and then come up with an agreed solution and pricing strategy.
     This reduces re-work down the track, which can delay the project delivery, and the chances of key functionality being forgotten or missed.
     I give a quote on the final product to be delivered, which means that the client knows what the project will cost, and when it will be delivered. Depending on the length of the project, then payment plans can be implemented for certain deliverables, which ensure both parties know what they will be receiving and when and not overly impacted by cash flow issues that can occur in large projects. This ensures that the client can afford to have the project completed in their own time-frame, and when they can afford to have each deliverable completed.

Where do you live?

     I currently live in Tasmania, Australia in little town called George Town.

Briefly describe your pertinent work and/or educational background.

     My work background has started in 1989 with the primary telecommunication company in Australia, working in their billing department as an analyst/programmer. This lead me to work for EDS and Hewlett Packard, still working on the billing systems for the telecommunication company. I also worked for a couple of small not for profit organisations helping them with the computer systems. I then worked for a company called Traffic Technologies.

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

     Based on the companies, the industries have mainly been in telecommunications and traffic management.

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?

     My rate may be higher than others through oDesk - yet the quality of the work I provide, and the time I put in up-front to understand the requirements from my clients mean less re-work in the end, and that all work is agreed to prior to commencing.

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 lot of the run of the mill programmers will get a brief understanding of what the client wants, then jumps in and starts building the database. They then find out down the track, that something has been missed, and need to go back and change the whole structure of the database and coding to accomplish what was missed. What I do differently, and this has been learned over 20 years, is to gather the business requirements from the client, document the requirements, and ensure that the client it happy with the requirements gathered. This gives the client a physical document that they can take to their key stake holders, to ensure that a crucial part of the work process isn't missed, and that all data and business rules are covered. This then means less work and re-work because of oversights.

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?

     Over the years, one of the biggest lessons I learnt is to have consistent naming conventions across the board, this way you don't get confused, and it helps other developers that may be brought in at a later stage to modify or create additions to the system. This has always served me well. I also try to reuse certain code that isn't proprietary to reduce the effort needed to build a system.

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?)

     If needed, and usually at a client's request, I will move away from the standard naming convention that I use to the client's requirement. In doing this, I do maintain a dictionary of the naming conventions that is given to the client so that they can give it to new programming staff in the future if needed.

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

     The main RDBMS that I have used extensively are Microsoft Access and SQL Server 7, 2000 and 2005.

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)

     In developing a website for Company X, the RDBMS that would be used would be dependant on the underlying operating system of the servers hosting the website and RDBMS. Saying that, I was involved in developing a website, with database for Telstra, and we used SQL Server 2005 and Visual Studio .Net.

What other technologies are you using for this website?

     The other technologies that could be used for the website and database are Apache, PHP, and MySQL. You could also use SharePoint, and have the Access database interact with SharePoint if your company has the relevant infrastructure in place to host.

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

     As this project, initially at this stage is purely an Access database, the main IDE that I will be using is Microsoft Access. I can use Project to lay out the timeline of the project for you and the deliverables.

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

     I have done work using .Net, and VB6. My background is in Microsoft Access. I can be flexible, but I no longer have those toolsets available to me.

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

     I give my Table names et al a singular name, as they are relating to an entity (ie Person). Yes a table can hold people, but at any point in time on the screen, the user is usually interacting with a single record.

How do you name your primary identity fields?

     The primary identity fields are prefixed with PK indicating a Primary Key.

Describe your relationship (if any) with WordPress?

     My use of WordPress has been as a user, not a programmer.

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

     The decision on stored procedures is determined on what they need to return, the complexity of the data being viewed, and the performance needed. The other alternatives are Views, and you need to look at performance as well with these.

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

     My background, experience working with both large and small companies, persistence to ensure quality work is produced and to ensure that the client gets what they are paying for is of my highest concern. My prospective client need to ensure that they get the best bang for dollar, and not have to be paying for re-work.